MySql Replace part of a string with escape characters

Question

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.

skumy · Accepted Answer · 2019-07-02 12:20:02Z

2

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), '/'))

answered Jul 2, 2019 at 12:20

skumy

214 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gaurav Lad · Accepted Answer · 2015-12-05 17:38:31Z

0

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.

answered Dec 5, 2015 at 17:38

Gaurav Lad

1,8081 gold badge17 silver badges31 bronze badges

5 Comments

danzo Over a year ago

Thanks Gaurav Lad. I tried with 4, 3 and 2 slashes and it has no effect. The message says "Your SQL query has been executed successfully", but nothing changes.

Gaurav Lad Over a year ago

Try this stackoverflow.com/questions/4271186/… And integrate my above answer with it.

Gaurav Lad Over a year ago

UPDATE vjfl_sliderimages SET data = REPLACE(data, '////myolddomain.com////images////', '////images////') WHERE data like '%////myolddomain.com////images////%'; Try this, let me know if it works.

danzo Over a year ago

Nope. "0 rows affected". Tried with 3 and 4 slashes.

Gaurav Lad Over a year ago

How much records does SELECT query returning your. Try running SELECT * FROM Table_name where FieldName like '////myolddomain.com////images////';

Naruto · Accepted Answer · 2015-12-05 18:14:37Z

0

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/');

edited Dec 5, 2015 at 18:14

answered Dec 5, 2015 at 17:55

Naruto

4,3591 gold badge24 silver badges32 bronze badges

3 Comments

danzo Over a year ago

Thanks Naruto. I tried that initially, but it returns "0 rows affected".

Naruto Over a year ago

can you place exact url....As i have checked in my local database and it is running perfectly...I have edited answer if your domain name was myolddomain.com/images/

danzo Over a year ago

This database was migrated from my local server to a live server. The URL on my local server was 192.168.10.15/myolddomain.com/images. The slider component used what it considered to be a relative URL of /myolddomain.com/images, which no longer works on the live server for obvious reasons. That's why there is a leading slash. Now that it's on the live server, the URL to the images are all wrong. I could manually edit them, but there are many and it would take hours.

Collectives™ on Stack Overflow

MySql Replace part of a string with escape characters

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related