Replace value within a comma-delimited string in MySQL?

Question

Suppose I have the following comma-delimited column value in MySQL: foo,bar,baz,bar,foo2

What is the best way to replace whatever is in the 4th position (in this case bar) of this string with barAAA (so that we change foo,bar,baz,bar,foo2 to foo,bar,baz,barAAA,foo2)? Note that bar occurs both in position 2 as well as position 4.

I know that I can use SUBSTRING_INDEX() in MySQL to get the value of whatever is in position 4, but have not been able to figure out how to replace the value in position 4 with a new value.

I need to do this without creating a UDF or stored function, via using only the standard string functions in MySQL (http://dev.mysql.com/doc/refman/5.5/en/string-functions.html).

possible duplicate of [Mysql select where field in csv string](http://stackoverflow.com/questions/4265463/mysql-select-where-field-in-csv-string) — MPelletier, Nov 07 '11 at 01:51
Relational databases and comma-delimited values don't really mix. Is it possible to alter your schema to be properly normalized? — tadman, Apr 22 '15 at 16:30

Niet the Dark Absol · Answer 1 · 2011-11-12T08:07:59.213

2

Hmm... maybe this?

SELECT @before := CONCAT(SUBSTRING_INDEX(`columnname`,',',3),','),
       @len := LENGTH(SUBSTRING_INDEX(`columnname`,',',4)+1
FROM `tablename` WHERE ...;

SELECT CONCAT(@before,'newstring',SUBSTRING(`columnname`,@len+1)) AS `result`
FROM `tablename` WHERE ...;

Replace things as needed, but that should just about do it.

EDIT: Merged into one query:

SELECT
    CONCAT(
        SUBSTRING_INDEX(`columnname`,',',3),
        ',newstring,',
        SUBSTRING(`columnname`, LENGTH(SUBSTRING_INDEX(`columnname`,',',4)+1))
    ) as `result`
FROM `tablename` WHERE ...;

That +1 may need to be +2, I'm not sure, but that should work.

edited Nov 12 '11 at 08:07

answered Nov 07 '11 at 01:49

Niet the Dark Absol

320,036
81
464
592

Is it possible to do it using a single SELECT statement and without using variables? – archmeta Nov 12 '11 at 05:53
As it happens, yes, since each variable is only used once. Let me edit my answer. – Niet the Dark Absol Nov 12 '11 at 08:05

score 0 · Answer 2 · edited May 23 '17 at 12:26

You first split your problem in two parts:

locate the comma and split the string in values separated by comma.
update the table with same string and some substring appended.

For the first part I would suggest you take a look here

And for the second part you should take a look here

One more thing there is no shortcut to any problem. You should not run from the problem. Take it as a challenge. Learn while you search for the answer. Best thing take guidance from here and Try to do more researching and efforts.

score 0 · Answer 3 · edited May 23 '17 at 12:18

0

Try this:

   UPDATE yourtable
   SET
      categories =
       TRIM(BOTH ',' FROM
         REPLACE(
           REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
       )
   WHERE
     FIND_IN_SET('2', categories)

taken from here The best way to remove value from SET field?

edited May 23 '17 at 12:18

Community

1
1

answered Aug 08 '16 at 10:25

LeonidF

1
3

Replace value within a comma-delimited string in MySQL?

3 Answers3

Linked

Related