View Full Version : Replace in MYSQL
03-06-2009, 01:47 AM
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?
03-06-2009, 08:25 AM
UPDATE table SET name = Replace( name, 'jon', '' ) WHERE name = 'jon';
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?
Powered by vBulletin® Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.