Find rows that contain same value inside comma separated values

Question

I have a varchar column, populated by another process where I have no control over, that is filled with comma separated values.

Now I need to find all rows where part of this column exists in that same column, in another row

example

declare @table table (value varchar(50))
insert into @table values ('NB,BD,FR'), ('BD,GK'), ('SL,SR')

select * from @table

so the table contains

value   
-----   
NB,BD,FR    
BD,GK   
SL,SR   

from the example above I would like to get

value   
-----   
NB,BD,FR    
BD,GK   

Because there is a value (in this case BD but can be anything) present in both rows

Can this be done in sql?

Answer 1

You could use clunky XML manipulation to convert comma separated values to rows:

DECLARE @table TABLE (value VARCHAR(50));
INSERT INTO @table VALUES
('NB,BD,FR'),
('BD,GK'),
('SL,SR');

WITH cte AS (
    SELECT value, node.value('.', 'varchar(10)') AS substr
    FROM @table
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(value, ',', '</x>,<x>') + '</x>' AS XML)) AS x(doc)
    CROSS APPLY doc.nodes('/x') AS n(node)
)
-- use your favorite technique to find the duplicate
SELECT value
FROM cte AS m
WHERE EXISTS (
    SELECT 1
    FROM cte AS x
    WHERE value <> m.value AND substr = m.substr
)

The CAST(... AS XML) part assumes that your data does not contain characters that have special meaning in XML. The nodes method will convert one row to many, rest is straight forward.