SQL substring replacing

Question

I am using HeidiSql and I have a database with ~1000 URL's. Example:

index.php?option=com_flexicontent&view=items&cid=283&id=33
index.php?option=com_flexicontent&view=items&cid=421&id=4411
index.php?option=com_flexicontent&view=items&cid=415&id=4375

What I have to do is to replace the cid= with values from 408 to 477 to cid=403

I have made a SQL script like this:

UPDATE jos_menu
SET link = REPLACE(link, "cid=411", 'cid=403')

But how do I change the cid= values 408 to 477, without making 70 REPLACEs?

how many rows are updated if you execute this `...REPLACE(link, 'cid=408', 'cid=477'`? — John Woo, Sep 12 '12 at 14:53
@njk that could work, but how to pass all the numbers to it? — Marbury, Sep 12 '12 at 15:08
@Marbury It appears you are using PHP. How are you passing those numbers to the query? — Kermit, Sep 12 '12 at 15:15
@njk I am not using PHP. Just writing a SQL script and need to use this function only once. — Marbury, Sep 13 '12 at 05:40

score 1 · Answer 1 · answered Sep 12 '12 at 15:15

I cant test this but try this

UPDATE jos_menu
SET link = REPLACE(link, 'cid=' + SUBSTRING(@str, CHARINDEX( 'cid=', @str) + 4, 3), 'cid=403')
WHERE Cast(SUBSTRING(@str, CHARINDEX( 'cid=', @str) + 4, 3) as Int) > 407 And Cast(SUBSTRING(@str, CHARINDEX( 'cid=', @str) + 4, 3) as Int) < 478

score 0 · Answer 2 · answered Dec 11 '12 at 16:14

0

Make a backup of the table or database in phpmyadmin and save it as a .cvs file. Use excel to do the replacements. Than save it as a .cvs file again, and import it to the database back.

answered Dec 11 '12 at 16:14

bobzrz8

129
1
3
12

SQL substring replacing

2 Answers2

Linked