from existing value Update column using comma-separated values in SQL Server
tbl
id name rev_code
1 aaa rev1,rev2,rev3
2 bbb rev2,rev3
3 ccc rev1,rev2
4 ddd rev3,rev2
i want update where rev_code='rev3' need to add end of with , like rev1,rev2,rev3,rev5
expecting output
id name rev_code
1 aaa rev1,rev2,rev3,rev5
2 bbb rev2,rev3,rev5
3 ccc rev1,rev2
4 ddd rev3,rev2,rev5
Your best option would be to normalize your database:
Get rid of that rev_code column and turn it into a new table, linked with a foreign key constraint to the current table.
If that's impossible, you can do something like this:
UPDATE tbl
SET rev_code = rev_code + ',rev5'
WHERE ','+ rev_code + ',' LIKE '%,rev3,%'
AND ','+ rev_code + ',' NOT LIKE '%,rev5,%'
2, without that trick you would get false positives on any member that contains 2 - like 21, 3245 and so on.
Stop storing comma separated values in a column it violates First Normal form. Consider changing your database design
At worst case use this update to get the result
UPDATE Yourtable
SET [rev_code] = [rev_code] + ',rev5'
WHERE Charindex('rev3', [rev_code]) > 0
rev_code columnOuch...you have your db design wrong so i will first suggest to create additional one-to-many table where you would store fields: originalTableRecordId | rev_code
then you find all records in that table that have particular rev_code value and add new one to those that meet criteria.
If you cant change your db structure then you have to go with either "WHERE rev_code like '%rev3%' " (which will be awfully slow) or with fulltext search (which is a huge overkill)
Try this, this will rev5 for the rev_code if there is rev3 and rev5 already not exists.
UPDATE tbl
SET rev_code = rev_code + ',rev5'
WHERE rev_code NOT LIKE '%rev5%' AND rev_code LIKE '%rev3%'
how about:
UPDATE table
SET rev_code = rev_code+',rev5'
WHERE rev_code LIKE '%rev3%' (OR RIGHT(rev_code,4) = 'rev3')
Try this,
UPDATE table1 SET rev_code=rev_code +',rev5'
where rev_code like '%rev3%' and Charindex('rev5', [rev_code])= 0
edited:
UPDATE table1 SET rev_code= rev_code +',rev5'
where rev_code IS NOT NULL and rev_code like '%rev3%' and Charindex('rev5', [rev_code])= 0
Check rev_code column size.
And as @Zohar Peled said, it is a bad idea while storing comma separated values.
Is storing a delimited list in a database column really that bad?
