Replace in MYSQL

[millsy007]

I am using the syntax: REPLACE(str,from_str,to_str)

to replace data in my table. However I have a slight issue I would like to address, when I have the following records

jon | jonathon | jonas | jonny

and I do the replace with

REPLACE(names, jon, '')

I am left with

| athon | as | ny

Is there a way that it can only replaces 'whole words' and not replace the string where it is part of a larger string?

[Old Pedant]

Sure...easy...

UPDATE table SET name = Replace( name, 'jon', '' ) WHERE name = 'jon';


NOW....

If you REALLY have records with delimited text strings in them (that is, if you really have a field called "names" that really has "jon | jonas" in it), then it looks like it's time to redesign the DB. Delimited text strings are, in general, "poison" in relational databases.

HAVING SAID THAT...

If you *KNOW* you have (for example) a space on each side of EVERY delimiter, then you can probably do this in 3 steps:

(1) put a delimiter on both front and rear of all values:
UPDATE table SET names = '| ' + names + ' |'

(2) now do the replace using delimiters:
UPDATE table SET name = Replace( names, '| jon |', '|' )

(3) Strip off the front and rear delimiters
UPDATE table SET name = Substring(names,2,Len(names)-4)
[not sure of syntax of the for MySQL, but you get the idea]

You can, with care, combine steps 1 and 2. For step 3, you need to make sure that the length is indeed > 4 (you might have collapsed '| jon |' into just '|' if it was only name in the string).

See why a DB redesign would be a good idea?