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