MySQL select where like path

Question

Setting a wordpress project to its staging environment, I have ran into an issue regarding paths that were set for the development environment, and don't fit the ones in staging.

So I need to update the paths in the database, from C:\xampp\htdocs\site.com to /var/www/site.com

At first, I tried replacing, the same way I replaced the urls:

update `wp_slider` set `url` = replace(`url`, 'http://local.', 'http://');

Then the paths:

update `wp_slider` set `path` = replace(`path`, 'C:\xampp\htdocs\site.com', '/var/www/site.com');

Which actually didn't work. Then I tried a SELECT to see what rows can I retrieve:

SELECT * FROM `wp_slider` WHERE `path` LIKE "%C:\xampp\htdocs\site.com%"

Which will return an empty result. What am I missing?

Forgot to mention, that I tried escaping the \ by doing \\ and I still get no result

A full path of what I'm trying to replace would be like: C:\xampp\htdocs\site.com/wp-content/plugins/slider/skins/slider\circle\circle.css

Answer 1

That's roughly the way to go:

mysql> SELECT REPLACE('C:\\xampp\\htdocs\\site.com\\foo\\bar.txt', 'C:\\xampp\\htdocs\\site.com', '/var/www/site.com');
+----------------------------------------------------------------------------------------------------------+
| REPLACE('C:\\xampp\\htdocs\\site.com\\foo\\bar.txt', 'C:\\xampp\\htdocs\\site.com', '/var/www/site.com') |
+----------------------------------------------------------------------------------------------------------+
| /var/www/site.com\foo\bar.txt                                                                            |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

If you get zero matches that's because your DB records do not contain what you think they do. Make sure you don't have blanks or control characters. If your MySQL client does not make it easy to spot such things, you can always use HEX():

SELECT path, HEX(path)
FROM wp_slider
WHERE path NOT LIKE "C:\\xampp\\htdocs\\site.com%"

Additionally, I'm not fully sure you can use \ as path separator in Unix systems. I suggest you replace it as well:

UPDATE wp_slider
SET path = replace(path, '\\', '/')
WHERE path IS NOT NULL

Update:

What I'm trying to explain is that your procedure is basically correct (except that escaping \ is not always optional):

mysql> CREATE TABLE wp_slider(
    -> path VARCHAR(2083)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO wp_slider (path) VALUES ('C:\\xampp\\htdocs\\site.com/wp-content/plugins/slider/skins/slider\\circle\\circle.cs
s');
Query OK, 1 row affected (0.04 sec)

mysql> UPDATE wp_slider SET path=REPLACE(path, 'C:\\xampp\\htdocs\\site.com', '/var/www/site.com');
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM wp_slider;
+----------------------------------------------------------------------------+
| path                                                                       |
+----------------------------------------------------------------------------+
| /var/www/site.com/wp-content/plugins/slider/skins/slider\circle\circle.css |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

If you don't get matches it's because your database contains different data than you think, such as (but not restricted to) whitespace or control characters:

mysql> TRUNCATE TABLE wp_slider;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO wp_slider (path) VALUES ('C:\xampp\htdocs\site.com/wp-content/plugins/slider/skins/slider\circle\circle.css');
Query OK, 1 row affected (0.02 sec)

mysql> UPDATE wp_slider SET path=REPLACE(path, 'C:\\xampp\\htdocs\\site.com', '/var/www/site.com');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM wp_slider;
+------------------------------------------------------------------------------+
| path                                                                         |
+------------------------------------------------------------------------------+
| C:xampphtdocssite.com/wp-content/plugins/slider/skins/slidercirclecircle.css |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In this last example, we forgot to escape \ when inserting and as a result we don't get a match when replacing because the input data is not what we thought it was.

Answer 2

You need to escape the backslashes: \\