View Full Version : sql 'smart' replace?

28 Nov 2007, 05:33 PM
firstly i'm pretty new to sql (specifically mysql) so forgive any blunders.

i need to do this in mysql and i'm having an issue trying to correctly replace occurrences of text within a body of text thats stored in the database.

currently i have something like this:

UPDATE table SET 'field' = replace('field', "old_text", "new_text")

and it somewhat works except it replaces ANY instance of the old text.
for example, a sentence that reads "the man went shopping in manhattan."

i want to replace all occurrences of the word "man" with "cat". if i use the above code, i get the following:

"the cat went shopping in cathattan"

i WANT to be able to get "the cat went shopping in manhattan".

i've tried adding spaces around the "old_text" like so -> " man " and that DOES pick up the word 'man' by itself without affecting manhattan, but unfortunately that would mean it would not be able to pick up "Man, I am awesome" or "You're awesome, Man". since it doesn't have a space preceding or proceeding it.

any help would be appreciated.