Replace value within a comma-delimited string in M

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).

Answer 1

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?

Answer 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.

Answer 3

You first split your problem in two parts:

1. locate the comma and split the string in values separated by comma.
2. 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.