CODEWITHSUNDEEP
mysqlstringsubstring
archmeta
archmeta

Reputation: 1127

Replace value within a comma-delimited string in MySQL?

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

Upvotes: 0

Views: 3760

Answers (3)

LeonidF
LeonidF

Reputation: 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?

Upvotes: 0

user4820255
user4820255

Reputation:

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.

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324750

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.

Upvotes: 2

Related Questions