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?

回答1:

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

回答2:

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.