CODEWITHSUNDEEP
sqlreplaceheidisql
Marbury
Marbury

Reputation: 21

SQL substring replacing

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?

Upvotes: 2

Views: 1107

Answers (2)

bobzrz8
bobzrz8

Reputation: 129

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.

Upvotes: 0

Fred
Fred

Reputation: 5808

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

Upvotes: 1

Related Questions