MySQL select where like path -

MySQL select where like path -

setting wordpress project staging environment, have ran issue regarding paths set development environment, , don't fit ones in staging.

so need update paths in database, c:\xampp\htdocs\site.com /var/www/site.com

at first, tried replacing, same way replaced urls:

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

then paths:

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

which didn't work. tried select see rows can retrieve:

select * `wp_slider` `path` "%c:\xampp\htdocs\site.com%"

which homecoming empty result. missing?

forgot mention, tried escaping \ doing \\ , still no result

a total path of i'm trying replace like: c:\xampp\htdocs\site.com/wp-content/plugins/slider/skins/slider\circle\circle.css

that's way 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 0 matches that's because db records not contain think do. create sure don't have blanks or command characters. if mysql client not create easy spot such things, can utilize hex():

select path, hex(path) wp_slider path not "c:\\xampp\\htdocs\\site.com%"

additionally, i'm not sure can utilize \ path separator in unix systems. suggest replace well:

update wp_slider set path = replace(path, '\\', '/') path not null

update:

what i'm trying explain procedure right (except escaping \ not optional):

mysql> create table wp_slider( -> path varchar(2083) -> ); query ok, 0 rows affected (0.06 sec) mysql> insert 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 * wp_slider; +----------------------------------------------------------------------------+ | path | +----------------------------------------------------------------------------+ | /var/www/site.com/wp-content/plugins/slider/skins/slider\circle\circle.css | +----------------------------------------------------------------------------+ 1 row in set (0.00 sec)

if don't matches it's because database contains different info think, such (but not restricted to) whitespace or command characters:

mysql> truncate table wp_slider; query ok, 0 rows affected (0.03 sec) mysql> insert 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 * wp_slider; +------------------------------------------------------------------------------+ | path | +------------------------------------------------------------------------------+ | c:xampphtdocssite.com/wp-content/plugins/slider/skins/slidercirclecircle.css | +------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

in lastly example, forgot escape \ when inserting , result don't match when replacing because input info not thought was.

mysql select where sql-like