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%’;

Answer

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.

Advertisement

Answer