MySQL “replace” is your friend
Need to replace all occurrences of a substring within loads of fields in a MySQL database table your using?
Try…
update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');
I never realised there was an actual “replace” method – especially seeing as I have seen many “self-rolled SQL script” attempts at it and assumed they were floating around because there was no straight-forward way of doing this. I guess I really must spend some time going over the man pages of MySQL (and/or just learn some more SQL in general!).
Due to the nature of the databases I work on (which are Oracle and not MySQL), running raw SQL commands on them is frowned upon (or just totally banned in the case of live DB’s). Probably the greatest thing about Apple WebObjects is that it maps your database schema into objects and controls all the reading/writing itself (I think WO was the first to successfully achieve this).
The result of these two facts is that my SQL skills are probably the most lacking within my development skill-set. Maybe my New-Year’s resolution will be to rely less on PHPMyAdmin when working on my home-brew projects!
Thanks Ben for the help. Your info was much more helpful than the MYSQL pages about replace.
Being self-taught, I stuck toes in the water with a LIMIT 10 on the end of my SQL and it didn’t work at all. I did find out that it’s comfortable with a WHERE clause and used that for potential damage control… then went for the gusto. Ben, you’re my HERO for today!
Thanks Ben,
that was exactly what I was looking for!
Ben, hello and thanks for this great tip. It is the only helpful link in google result page for keyword “mysql replace substring”. Thanks, great job!
Ben, this tip was exactely what I needed.
Thanks!
Works a treat, thanks!
Excellent, saved me hours!!