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 — MPelletier
– MPelletier, Commented Nov 7, 2011 at 1:51
Relational databases and comma-delimited values don't really mix. Is it possible to alter your schema to be properly normalized? — tadman
– tadman, Commented Apr 22, 2015 at 16:30

Niet the Dark Absol · Accepted Answer · 2011-11-12 08:07:59Z

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, 2011 at 8:07

answered Nov 7, 2011 at 1:49

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

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

2 Comments

archmeta Over a year ago

Is it possible to do it using a single SELECT statement and without using variables?

Niet the Dark Absol Over a year ago

As it happens, yes, since each variable is only used once. Let me edit my answer.

Community · Accepted Answer · 2017-05-23 12:26:39Z

0

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.

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Apr 22, 2015 at 16:35

user4820255

Comments

Community · Accepted Answer · 2017-05-23 12:18:35Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Aug 8, 2016 at 10:25

LeonidF

13 bronze badges

Collectives™ on Stack Overflow

Replace value within a comma-delimited string in MySQL?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related