Menu
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?
357
The TRIM() function returns a string that has unwanted characters removed. Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
You cannot give underscore in table name. If you still want to create a new table with underscore, surround it using backticks, not single quotes.
You can use the REPLACE function :
REPLACE(str,from_str,to_str)Returns the string
strwith all occurrences of the stringfrom_strreplaced by the stringto_str.REPLACE()performs a case-sensitive match when searching forfrom_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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
