Update the column with Specific Comma Separated Value

Question

I have created a table which stores the value in comma separated form. Is there any mechanism which achieve the following result?

I have the column named qty in table and it stores the value in following format:

Initially Column Contain:-  1,5,8,9,7,10,5

Now i want to update the third value which is 8 by 2

So Final Answer that Column contain is:-  1,5,2,9,7,10,5

This is why we don't put comma-separated strings representing lists of values as column values. PS What did you find when you googled various clear statements of your problem? Eg your title plus tags? (Although your title should be clearer.) This is a faq answered by a trivial googling. And the answerers should all have marked this as a duplicate. — philipxy, Jun 07 '17 at 06:05
Write a procedure to parse the string and pass parameters of position and replacement value. But you should not store data in this way. — P.Salmon, Jun 07 '17 at 06:10
I disagree with the marking of this as a duplicate. Neither of the two answers above answers the question posed. An opinion that this in a bad way to store data is a not an answer. — Rob MacMillian, Jun 06 '19 at 22:00

score 3 · Answer 1 · answered Jun 07 '17 at 06:07

3

You can use string replace, knowing that this is not a well formed normalized database.

Your database shall follow the standard normalization patterns in order to obtain a solid relationship between tables where performance, usability and maintenance will be much better. Your table is not even 1NF.

Though this query for now helps, but consider changing your structure.

   update TBL 
       set column = replace(column , '8', '2') 
     where your_condition

Check this link to get to know more about normalization and their forms : Normalization

answered Jun 07 '17 at 06:07

KAD

10,972
4
31
73

Good point about normalization, up voted this. – Elie Nassif Jun 07 '17 at 06:11
It will fail in below case: update wp_posts set test_csv = '1,2,9,10,101,11,121,1248,8956' where ID = 116; and then update wp_posts set test_csv = replace(test_csv,'10', '101') where ID = 116; – Rahul Aug 12 '19 at 12:31

score 1 · Answer 2 · answered Jun 07 '17 at 06:07

Let's suppose there is column SampleID in Student table:

value of Name in table is : '1,5,8,9,7,10,5', and you want to replace '8' with '2'

You can use the following query:

UPDATE dbo.Student
SET [SampleID] = REPLACE([SampleID], '8', '2')

You can modify this further to get more accuracy.

lalithkumar · Answer 3 · 2017-06-07T06:14:19.017

1

Actually you should not store the values with comma because this will cause SQL injection attacks and no of reasons behind that.But now to handle use below query:

     update table_name set coulmn_name=REPLACE('1,5,8,9,7,10,5', '8', '2') where 1
 and conditions;

edited Jun 07 '17 at 06:14

answered Jun 07 '17 at 06:07

lalithkumar

3,480
4
24
40

Blank · Answer 4 · 2017-06-07T06:13:24.470

Use several string functions can do that:

update yourtabel
set yourcol = 
    concat(
        substring_index(yourcol, ',', 2), -- this will get string without trailing comma before third value
        ',',
        '2', -- the value you want to update to third value
        ',',
        substring_index(
            yourcol,
            ',',
            2 - (length(yourcol) - length(replace(yourcol, ',', '')))
        ) -- this will get string without heading comma after third value
     )

Here is a demo in SQLFiddle.

substring_index:https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index
length:https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_length
replace:https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace

Update the column with Specific Comma Separated Value

4 Answers4