str_replace in MySQL?

[Mong]

Hola, not been on here for ages... but I knew where to turn for geeky assistance!

I'm no good with MySQL. Is there a way I can make it search for a particular character, and replace it with another? Or do I have to write a script (PHP I guess) to do it?

Cheers!

M.

[meglamaniac]

afaik you'll have to do it in a secondary language, like PHP as you suggested.
My knowledge of SQL is far from extensive so I may be corrected.

[Dante Hicks]

http://dev.mysql.com/doc/mysql/en/string-functions.html

Try there.

Would :

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str.


mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

Be useful, in some sort of sub-query context?

[Mong]

Cheers - shoulda readTFM

I'm gonna try it on a copy of the DB first!

[Karhig]

Originally Posted by Dante Hicks

http://dev.mysql.com/doc/mysql/en/string-functions.html

Try there.

Would :


Be useful, in some sort of sub-query context?

I don't know for certain, but I suspect that that wouldn't work. The description of REPLACE states that it replaces one string with another only when outputting the query result to the outside world. It sounded as though the threa poster wanted to actually alter the values stored in the database itself. REPLACE would not do this. It might fool you since everytime you queried the database it would return what you expect so long as you left the REPLACE in there, but if you removed the REPLACE it would return to what was originally in the database. If this is all you need the go for it, if you are trying to actually alter what is stored in the database then you will probably need to write a script that alters each row in the database. Shouldn't be too difficult, a couple of lines of code at best.

[queball]

Originally Posted by Karhig

I don't know for certain, but I suspect that that wouldn't work. The description of REPLACE states that it replaces one string with another only when outputting the query result to the outside world. It sounded as though the threa poster wanted to actually alter the values stored in the database itself. REPLACE would not do this. It might fool you since everytime you queried the database it would return what you expect so long as you left the REPLACE in there, but if you removed the REPLACE it would return to what was originally in the database. If this is all you need the go for it, if you are trying to actually alter what is stored in the database then you will probably need to write a script that alters each row in the database. Shouldn't be too difficult, a couple of lines of code at best.

An UPDATE would do it, which (once you knew about REPLACE) would be obvious to anyone who knew some SQL.

Just to say the query your looking for works like this:

UPDATE table SET field=REPLACE(table.field, ' ','') WHERE 1;

This would for example remove the spaces from the field.