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%';
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.