MySQL query to replace spaces in a column with underscores

mysql replace space
Mithun Sreedharan picture Mithun Sreedharan · Nov 27, 2009 · Viewed 50.7k times · Source

I have a MySQL database table 'photos' with a column 'filename'. I need to replace the spaces in the filename column values with underscores. Is it possible with a single/multiple query? If so how?

Answer

Pascal MARTIN picture Pascal MARTIN · Nov 27, 2009

You can use the REPLACE function :

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str.
REPLACE() performs a case-sensitive match when searching for from_str.

So, to replace all occurences of a character by another one in all lines of a table, something like this should do :

update photos set filename = replace(filename, ' ', '_');

ie, you search for ' ' in the column filename and use '_' instead ; and put the result back into filename.

Related questions

MySQL string replace

I have a column containing urls (id, url): http://www.example.com/articles/updates/43 http://www.example.com/articles/updates/866 http://www.example.com/articles/updates/323 http://www.example.com/articles/updates/seo-url http://www.example.com/articles/updates/4?something=…

Read More
How can I use mySQL replace() to replace strings in multiple records?

We have a database that has a bunch of records with some bad data in one column, in which an embedded editor escaped some stuff that shouldn't have been escaped and it's breaking generated links. I want to run a …

Read More
MySQL search and replace some text in a field

What MySQL query will do a text search and replace in one particular field in a table? I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

Read More