-2

I would like to know how to replace my comma separated array to another comma separated array in mysql database. Have any method to replace without getting values from database only insert method.

Example:

X table Y column have this comma separated array: 1,3,2,6,3,4 I want to change this 3,2,6 values to 5,8,9. Finally array should be 1,5,8,9,3,4 in database.

I want to it without getting data from mysql database and also without giving privies value which means without giving 3,2,6. That means update this array from 2nd value of array. If it is not possible, please give me another possible method.

ind
  • 139
  • 1
  • 14

2 Answers2

0

Of course, comma separated arrays are just text strings as far as the database is concerned. So you can do this to make the change you want.

 UPDATE X SET Y = REPLACE(Y,'3,2,6','5,8,9') WHERE Y = '1,3,2,6,3,4'

Generally speaking, putting comma-separated values in single SQL columns makes it hard to update those columns, or search for values, in any SQL-friendly fashion.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

Unfortunately, MySQL doesn't have something like REGEXP_REPLACE function, so manipulating strings containing comma-separated lists is quite tricking task.

For your particular case you could use this:

UPDATE X SET Y = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', Y, ','), ',<SRC>,', ',<DST>,') ) WHERE ...;

Replacing <SRC> and <DST> with your 3,2,6 and 5,8,9 respectively, you'll get desired result.

Styx
  • 9,863
  • 8
  • 43
  • 53