mysql - update table use regexp

Question

SELECT id FROM people WHERE id REGEXP 'id-(\d)*';

result

id-1
id-2
id-3

This above query works but when i run update query below, it don't affect any rows

UPDATE people SET id = REPLACE(id, 'id-(\d)*', 'id-4');
0 row(s) affected

Somebody can explain for me, what wrong ?

MySQL's regex engine does not do replacements or capture groups. It's purely for matching in a WHERE clause. — Marc B
– Marc B, Commented Dec 20, 2011 at 4:24
You would be better off if you re-did the DB structure. This seems like madness. — tereško
– tereško, Commented Dec 20, 2011 at 5:18
@tereško : it is only a example. i want to update the value in a field and it very good for me if i can use something like Regexp in mysql — Chameron
– Chameron, Commented Dec 20, 2011 at 11:07

Somnath Muluk · Accepted Answer · 2011-12-20 05:35:43Z

1

REPLACE() doesn't search for regular expressions. Try this. But regexp_replace() function is supported by some of the databases.

This works for oracle, PostgreSQL. It doesn't work for MySQL.

 UPDATE people  SET id  = regexp_replace(id ,'id-(\d)*' , 'id-4');

edited Dec 20, 2011 at 5:35

answered Dec 20, 2011 at 4:23

Somnath Muluk

58.3k39 gold badges225 silver badges230 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ajreal Over a year ago

the question is tagged to mysql, why would you suggest something that is irrelevant ?

Somnath Muluk Over a year ago

I specifically written test cases for different databases. You may see I have written comment in question section for asking database name. I thought this answer might help for thinking. Should I delete answer?

ajreal Over a year ago

well, now the OP has tagged to mysql, then your answer is become irrelevant

Chameron Over a year ago

@all : thanks for sharing , now i know about the regexp_replace with oracle and PostgreSQL. Do not need to delete your answer ^^!

snowflake Over a year ago

@all This answer is definitively usefull, in the update can be processed offline user can unload / load into oracle or postgresql / unload / load back into mysql

Amadan · Accepted Answer · 2011-12-20 04:21:45Z

0

REPLACE does not use regular expressions, only vanilla strings. You're searching for the literal string 'id-(\d)*', not the 'id-' followed by any number of digits. Presumably, you do not have such a weird string in your database.

answered Dec 20, 2011 at 4:21

Amadan

200k23 gold badges252 silver badges321 bronze badges

Comments

Sudhir Bastakoti · Accepted Answer · 2011-12-20 04:23:07Z

0

REPLACE() does text replacement. There is no regular expression replacement in MySQL. Its better you perform REGEX with some server side scripting.

answered Dec 20, 2011 at 4:23

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

1 Comment

Chameron Over a year ago

What i should use instead REPLACE in this case ?

Collectives™ on Stack Overflow

mysql - update table use regexp

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related