MySQL Regex Replace Query

Question

I have a field with this value:

TEST:ATEST:TESTA

And I want to replace "TEST" with "NEW", I have tried this query:

UPDATE `table` SET `field` = REPLACE(`field`, 'TEST', 'NEW') WHERE `field` REGEXP 'TEST';

The result was:

NEW:ANEW:NEWA

Q: How could I do the replacement query so the result would be like this:

NEW:ATEST:TESTA

So the text `TEST` may appear in any place or just on the first place ? — Abhik Chakraborty, Apr 17 '15 at 13:44
please consider following solution here: http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Andriy, Apr 17 '15 at 13:54
Good catch @Abhikda, +1, So if TEST appear in first place we can use “^” to match the beginning — mysqlrockstar, Apr 17 '15 at 13:54
@Abhik It may appear in any place and some times the whole field would be the "TEST" word only. — iTesTi, Apr 17 '15 at 13:56
@iTesTi with regex you can select the data but can not modify it. You need some sort string manipulation for that purpose, check answer from Gordon. — Abhik Chakraborty, Apr 17 '15 at 14:05

score 0 · Accepted Answer · answered Apr 17 '15 at 13:49

It is a bit of a pain, but you can do it this way:

UPDATE `table`
    SET field = substr(REPLACE(concat(':', field, ':'), ':TEST:', ':NEW:'),
                       2, length(REPLACE(concat(':', field, ':'), ':TEST:', ':NEW:')) - 2)
    WHERE concat(':', field, ':') LIKE '%:TEST:%';

I prefer LIKE to REGEXP because there is the hope of being able to use an index. That is not a possibility in this case, but there is the hope.

This is delimiting the values with colons at the beginning and the end, and only replacing fully delimited values. The trick is to then remove the additional colons.

score 0 · Answer 2 · answered Apr 17 '15 at 15:14

You can try http://sqlfiddle.com/#!9/4e66b/3

so the update query is (if table name = table1, field name = field1, and there is unique column id):

UPDATE `table1` 
INNER JOIN
  (SELECT id, 
    @set_idx:=FIND_IN_SET('TEST',REPLACE(field1,':',',')),
    @set_size:=LENGTH(field1)-LENGTH(REPLACE(field1,':',''))+1,
    CASE 
      WHEN @set_idx=1 THEN CONCAT('NEW',SUBSTRING(field1, 4))
      WHEN @set_idx>1 THEN CONCAT(SUBSTRING_INDEX(field1, ':',@set_idx-1),':NEW', IF(@set_size>@set_idx,CONCAT(':',SUBSTRING_INDEX(field1, ':',-(@set_size-@set_idx))),''))
    END as new
  FROM table1
  WHERE `field1` REGEXP '(^TEST$)|(^TEST:)|(:TEST$)|(:TEST:)'
   ) t
ON t.id = table1.id
SET table1.field1 = t.new;

MySQL Regex Replace Query

2 Answers2