CODEWITHSUNDEEP
mysqlreplacespace
Mithun Sreedharan
Mithun Sreedharan

Reputation: 51274

MySQL query to replace spaces in a column with underscores

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?

Upvotes: 26

Views: 62985

Answers (2)

Don
Don

Reputation: 4663

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

Upvotes: 7

Pascal MARTIN
Pascal MARTIN

Reputation: 400932

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.

Upvotes: 67

Related Questions