3

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.

danzo
  • 301
  • 1
  • 5
  • 18

3 Answers3

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), '/'))
skumy
  • 21
  • 3
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.

Gaurav Lad
  • 1,788
  • 1
  • 16
  • 30
  • 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. – danzo Dec 05 '15 at 17:52
  • Try this http://stackoverflow.com/questions/4271186/how-can-i-use-mysql-replace-to-replace-strings-in-multiple-records And integrate my above answer with it. – Gaurav Lad Dec 05 '15 at 19:01
  • 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. – Gaurav Lad Dec 05 '15 at 19:03
  • Nope. "0 rows affected". Tried with 3 and 4 slashes. – danzo Dec 05 '15 at 20:27
  • How much records does `SELECT` query returning your. Try running `SELECT * FROM Table_name where FieldName like '////myolddomain.com////images////';` – Gaurav Lad Dec 06 '15 at 05:27
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/');
Naruto
  • 4,221
  • 1
  • 21
  • 32
  • Thanks Naruto. I tried that initially, but it returns "0 rows affected". – danzo Dec 05 '15 at 18:13
  • 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/ – Naruto Dec 05 '15 at 18:13
  • This database was migrated from my local server to a live server. The URL on my local server was http://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. – danzo Dec 05 '15 at 18:38