MySQL REPLACE variable string

问题:

I want to use MySQL query to change a link .

the link is like this :

http://website.com/click.php?ad_client=blablabla&add_id=548124&more=stuffhere

if I know the add_id number this is easy :

UPDATE table SET name = REPLACE(name, '&add_id=548124', '')

The problem is I have to change 5000 lines and I don't know the add_id number ... so what would be a correct mysql replace() code to remove &add_id=somenumber ??

回答1:

USE This....

 UPDATE table 
    SET name = CONCAT(SUBSTRING(name , 1, 
                    INSTR(name ,'&add_id') - 1),SUBSTRING(name , 
                    INSTR(name , '&more'), 
                    LENGTH(name ) - INSTR(name , '&add_id')))

回答2:

Either you can do it via UDF - SO Answer or you can simply write PHP code which will replace value & update column again in table.

回答3:

I would create a stored procedure that uses a cursor to iterate over each row that needs updating.

In the procedure I would find the link to replace and then replace them, one by one.

I've made a sqlfiddle to show how you can get the part to replace inside an select.

I think this approach is clean and easy to read but it's possible to write this a update (that will most likely be hard to read).

回答4:

• first to see that it works :

  SELECT 'http://website.com/click.php?ad_client=blablabla&add_id=548124&more=stuffhere' INTO @link;
  SELECT @link as full_link, SUBSTR(@link,LOCATE('&',@link),LOCATE('&',@link,LOCATE('&',@link)+1)-LOCATE('&',@link)) as remove_part, REPLACE(@link,SUBSTR(@link,LOCATE('&',@link),LOCATE('&',@link,LOCATE('&',@link)+1)-LOCATE('&',@link)),'') as final_link

And now for your UPDATE:

UPDATE table SET name = REPLACE(name,SUBSTR(name,LOCATE('&',name),LOCATE('&',name,LOCATE('&',name)+1)-LOCATE('&',name)),'')

回答5:

try this with REPLACE

 UPDATE Table1
 SET name = REPLACE(if(name like '%add_id=%','' , name ), 

 '&add_id=' , '' )

DEMO HERE