Search code examples
mysqlsqlbackslash

sql query to replace backslashes '\\' with '\/'

I know very little about sql queries but I need a query to replace '\\' with '\/'

I have a Wordpress database where there are characters in a long text string that look like this in phpMyAdmin Browse.

'\\uploads\\photos\\'

It needs to be '\/uploads/photos\/'. I want a query to find '\\uploads\\photos\\' so I can make sure the query is working.

I also want another query to permanently replace '\\uploads\\photos\\' with '\/uploads\/photos\/'.

The below query that finds photos returns results, but that's as far as I got.

SELECT * 
FROM `mytable`
WHERE `mycolumn` LIKE '%photos%'

Thank you forpas for the excellent solution!

For future readers:
When I migrated my wordpress database from local online, all in one migration plugin missed the paths in wonderplugin gallery that I am using. Thus my requirement for the query.

This double backslashed \ text was in a long string and I was concerned that there were more double backslashes that could get changed. So adding on to the solution provided by forpas, I more accuratly targeted the path text. mytable was actually named wp_nonxenon_wonderplugin_gridgallery, and mycolumn was named data. This is the resultant query that will save me much work in the future.

UPDATE wp_nonxenon_wonderplugin_gridgallery SET data = REPLACE(data, 'wp-content\\uploads\\photos\\', 'wp-content\/uploads\/photos\/') WHERE data LIKE '%photos%';

Solution

  • You must escape each backslash with a double backslash:

    SELECT REPLACE(mycolumn, '\\\\', '\\/') 
FROM mytable
WHERE mycolumn LIKE '%photos%';

    Or you can update the table:

    UPDATE mytable
SET mycolumn = REPLACE(mycolumn, '\\\\', '\\/')
WHERE mycolumn LIKE '%photos%';

    and the column will contain the values as you want them.

    See the demo.