CODEWITHSUNDEEP
mysqlmysql-workbench
VKS
VKS

Reputation: 567

How to replace the exact match and not the substring in comma separated strings in MySQL?

I have a column with comma separated values and I want to replace the exact matched value and not the substring.

current column value = 'ab,aba,ab,abc'

UPDATE table SET col1 = INSERT(col1, LOCATE('ab', 'ab,aba,ab,abc'), CHAR_LENGTH('ab'), 'BA') WHERE FIND_IN_SET('ab', 'ab,aba,ab,abc');

I expect the column value to be 'BA, aba, BA, abc'. But it is not working as expected. What am I doing wrong?

Upvotes: 1

Views: 484

Answers (1)

forpas
forpas

Reputation: 164139

After you concatenate a ',' at the start of the column and at the end of the column and replace all ',' with ',,', the replacement of all 'ab's can be done by searching for ',ab,' and replacing it with ',BA,'.
Finally remove the leading and trailing ',':

UPDATE tablename 
SET col = REPLACE(
            TRIM(',' FROM REPLACE(CONCAT(',', REPLACE(col, ',', ',,'), ','), ',ab,', ',BA,')), 
            ',,', 
            ','
          )
WHERE FIND_IN_SET('ab', col);

Or, if you want to pass 'ab' and 'BA' as parameters:

UPDATE tablename 
SET col = REPLACE(
            TRIM(',' FROM REPLACE(CONCAT(',', REPLACE(col, ',', ',,'), ','), CONCAT(',', 'ab', ','), CONCAT(',', 'BA', ','))), 
            ',,', 
            ','
          )
WHERE FIND_IN_SET('ab', col);

Replace ?1 with the string that you want to replace ('ab') and ?2 with the replacement string ('BA').

See the demo.

Upvotes: 1

Related Questions