mysql update with regexp

Question

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.

Answer 1

I have unique requirement where I need to replace inactive owner username. Where username contians INACITVE followed by village id. So I have used concat() funacion inside replace() function to replace dynamically.

 update Owner set username = replace(username, concat('_INACTIVE_',village_id) ,'') 
where village_id = 3363010;

Answer 2

You can't: Mysql doesn't support regex-based replace.

See this SO question for a work-around.

Answer 3

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."%'");
}