Find value in comma-separated list

Question

I have lists like this stored in a field in SQL:

Id MyListField
1  soy, meat, milk, wheat, nuts
2  soy, meat, nuts, milk, wheat
3  nuts
4  walnuts, nutshell, nuts
5  nuts, beans
6  walnuts, hazel nuts
7  nutshell, nutsize

This is not normalized and I can't change it. I now have to find nuts and replace them with insane (but keep e. g. hazel nuts and nutshell).

A Find is relatively easy, there are four cases and I have the placeholder character % at my disposal:

SELECT * FROM MyTable WHERE 
    MyListField LIKE 'nuts' OR 
    MyListField LIKE 'nuts, %' OR 
    MyListField LIKE '%, nuts' OR 
    MyListField LIKE '%, nuts, %'

But a Replace is hard, because I don't have placeholder or startofstring/endofstring characters:

UPDATE MyTable SET MyListField = 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    MyListField, 
                    ', nuts, ' , 
                    ', insane, '
                ), 
                ????, 
                ????
            ),
            ????, 
            ????
        ),
        ????, 
        ????
    )
WHERE 
    MyListField LIKE 'nuts' OR 
    MyListField LIKE 'nuts, %' OR 
    MyListField LIKE '%, nuts' OR 
    MyListField LIKE '%, nuts, %'

I can easily replace it in the middle of the string, but not at the start or the end. Or can I?

I am using SQL Server 2008, if that matters.

Split the data into rows using a string splitting function, then replace the rows where the item matches, and parse everything back together using the for xml path trick — James Z, Jul 09 '15 at 15:20
This is quite similar question: http://stackoverflow.com/questions/29574811/replace-semicolons-separated-values-with-another-table-values — James Z, Jul 09 '15 at 15:25

stovroz · Accepted Answer · 2015-07-09T15:44:51.507

2

If you concatenate the field between , and _, then you only have one case to look for.

e.g.

update MyTable set MyFieldList = replace(', ' + MyFieldList + ',', ', nuts,', ', insane,')
update MyTable set MyFieldList = substring(MyFieldList, 3, len(MyFieldList) - 3)

edited Jul 09 '15 at 15:44

answered Jul 09 '15 at 15:24

stovroz

6,835
2
48
59

score 2 · Answer 2 · answered Jul 09 '15 at 15:27

By putting a comma at the start and end of the list, each element is wrapped in a leading ", " and a trailing ",".

Then your replace method becomes easy....

REPLACE(', ' + MyListField    + ',', 
        ', ' + @termToReplace + ',', 
        ', ' + @replacement   + ',')

Finally, strip the leading and trailing commas.

score 0 · Answer 3 · answered Jul 09 '15 at 15:23

You could use something like

case 
  when Field = @val then @replaceVal
  when Field like @val + ', %' then 
   @replaceVal + substring(Field, len(@val) + 1, len(Field))
  when Field like '%, ' + @val then
   left(Field, len(Field) - len(@val)) + @replaceVal

Just add all the remaining options, and you should be fine.

score 0 · Answer 4 · edited May 23 '17 at 10:26

With all that help, it was easier than I thought it to be.

UPDATE MyTable 
SET MyListField =
    SUBSTRING(
        REPLACE(
            CONCAT(', ', MyListField, ', '), 
            ', nuts, ', 
            ', insane, '
        ),
        3,
        DATALENGTH(MyListField)+LEN('insane')-LEN('nuts')
    )
WHERE CONCAT(', ',MyListField,', ') LIKE '%, nuts, %'

This works as long as there is one or zero occurrences in the string. If there can be more than one, it will fail, but this does not matter to me. If it matters to you, feel free to multiply the (LEN-LEN) part with How do you count the number of occurrences of a certain substring in a SQL varchar?

Find value in comma-separated list

4 Answers4