How to compare two comma-separated strings, and return TRUE if there is at least 1 match

Question

I have to variables that contain comma-separated strings:

@v1 = 'hello, world, one, two'
@v2 = 'jump, down, yes, one'

I need a function that will return TRUE if there is at least one match. So in the above example, it would return TRUE since the value 'one' is in both strings.

Is this possible in SQL?

Answer 1

Use a split function (many examples here - CLR is going to be your best option in most cases back before SQL Server 2016 - now you should use STRING_SPLIT()).

Once you have a split function, the rest is quite easy. The model would be something like this:

DECLARE @v1 VARCHAR(MAX) = 'hello, world, one, two',
        @v2 VARCHAR(MAX) = 'jump, down, yes, one';

SELECT CASE WHEN EXISTS 
(
  SELECT 1 
    FROM dbo.Split(@v1) AS a
    INNER JOIN dbo.Split(@v2) AS b
    ON a.Item = b.Item
)
THEN 1 ELSE 0 END;

You can even reduce this to only call the function once:

SELECT CASE WHEN EXISTS 
(
  SELECT 1 FROM dbo.Split(@v1)
  WHERE ', ' + LTRIM(@v2) + ',' 
    LIKE '%, ' + LTRIM(Item) + ',%'
) THEN 1 ELSE 0 END;

On 2016+:

SELECT CASE WHEN EXISTS 
(
  SELECT 1 FROM STRING_SPLIT(@v1, ',')
  WHERE ', ' + LTRIM(@v2) + ',' 
    LIKE '%, ' + LTRIM([Value]) + ',%'
) THEN 1 ELSE 0 END;