I want to remove something from my table 1) 32) 121) 1000)... the format is number + )
I tried this code.
UPDATE articles SET
title= REPLACE(title,'\d)', '' )
WHERE title regexp "\d)*"
Nothing happened in phpmyadmin, how to write correct? Thanks.
You can't: Mysql doesn't support regex-based replace.
See this SO question for a work-around.
121)? UPDATE articles SET title= REPLACE(title,'121)', '' ) WHERE title regexp "121)*" still not work, should i add-slashes to the )?
like: UPDATE articles SET title = REPLACE(title,'121)', '' ) WHERE title like '%121)%';. Or just leave out the where clause - rows without 121) won't be changed.Finally, I use some php to solve this problem with a quickly method.
for ($i=1; $i<=9999; $i++){
$my_regex = $i.')';
mysql_query("UPDATE articles SET title = REPLACE(title,'".$i."', '' ) where title like '%".$i."%'");
}
I have unique requirement where I need to replace inactive owner username. Where username contains _INACTIVE_ followed by village id. So I have used the CONCAT() function inside a REPLACE() function, to dynamically replace.
update Owner set username = replace(username, concat('_INACTIVE_',village_id) ,'')
where village_id = 3363010;
As an alternative, depending on the size of the table, you could do a workaround with substring function.
