CODEWITHSUNDEEP
mysqlstringurlreplace
danzo
danzo

Reputation: 301

MySql Replace part of a string with escape characters

I'm trying to do a simple update the old url in a database table with a new relative url, but I keep getting "0 rows affected". I think it might have to do with the escape characters in the string?

The URLs in the column 'data' now are structured with the backslash. Here's my SQL:

 UPDATE vjfl_sliderimages
 SET data = REPLACE(data, '\/myolddomain.com\/images\/', '\/images\/')

This should work to change the URL of every image from myolddomain.com/images/ to /images/ but for some reason it just doesn't have any affect.

Upvotes: 3

Views: 2234

Answers (3)

skumy
skumy

Reputation: 21

For anyone who has this issue, use CONCAT SQL function with CHAR(92) which correspond to '\' ASCII char.

Example:

UPDATE vjfl_sliderimages SET data = REPLACE(data, CONCAT(CHAR(92), '/myolddomain.com', CHAR(92), '/images', CHAR(92), '/'), CONCAT(CHAR(92), '/images', CHAR(92), '/'))

Upvotes: 2

Naruto
Naruto

Reputation: 4329

As it is forward slash so no need of escape symbol.

UPDATE vjfl_sliderimages SET data = REPLACE(data,'/myolddomain.com/images/', '/images/');

Edit:- if the previous url was myolddomain.com/images/

then query must be

UPDATE vjfl_sliderimages SET data = REPLACE(data,'myolddomain.com/images/', '/images/');

Upvotes: 0

Gaurav Lad
Gaurav Lad

Reputation: 1808

Escape characters(/) are treated differently in MySQL or SQL VARCHAR fields. Try 

 UPDATE vjfl_sliderimages
 SET data = REPLACE(date, '////myolddomain.com////images////', '////images////');

If 4 splashes doesn't work then try for 3.

Upvotes: 0

Related Questions