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.

score 8 · Accepted Answer · edited May 23 '17 at 12:02

8

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

See this SO question for a work-around.

edited May 23 '17 at 12:02

Community

1
1

answered May 05 '12 at 19:37

Bohemian

412,405
93
575
722

so how to replace just `121)`? `UPDATE articles SET title= REPLACE(title,'121)', '' ) WHERE title regexp "121)*"` still not work, should i add-slashes to the `)`? – cj333 May 05 '12 at 19:42
2

Just use `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. – Bohemian May 05 '12 at 19:46

score 2 · Answer 2 · answered May 05 '12 at 20:07

2

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

answered May 05 '12 at 20:07

cj333

2,547
20
67
110

score 1 · Answer 3 · answered Nov 30 '21 at 07:21

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;

score 0 · Answer 4 · answered May 05 '12 at 19:40

0

As an alternative, depending on the size of the table, you could do a workaround with substring function.

answered May 05 '12 at 19:40

Andrius Naruševičius

8,348
7
49
78

mysql update with regexp

4 Answers4

Linked