CODEWITHSUNDEEP
sql-servercsv
loyalflow
loyalflow

Reputation: 14879

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

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?

Upvotes: 6

Views: 16069

Answers (2)

anon
anon

Reputation:

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;

Upvotes: 13

supergrady
supergrady

Reputation: 1322

You can use CTEs to split your string into xml nodes, then insert the words into table variables. Joining the table variables will reveal any matches

DECLARE @v1 VARCHAR(200) = 'hello, world, one, two'
DECLARE @v2 VARCHAR(200) = 'jump, down, yes, one'
DECLARE @v1Words TABLE (word VARCHAR(100))
DECLARE @v2Words TABLE (word VARCHAR(100))

;WITH cteSplitV1 AS( 
SELECT CAST('<word>' + REPLACE(@v1,', ','</word><word>') + '</word>' AS XML) AS words) 
INSERT INTO @v1Words(word)
    SELECT word.x.value('.','VARCHAR(100)') AS [word]
    FROM cteSplitV1
    CROSS APPLY words.nodes('/word') AS word(x)

;WITH cteSplitV2 AS( 
SELECT CAST('<word>' + REPLACE(@v2,', ','</word><word>') + '</word>' AS XML) AS words) 
INSERT INTO @v2Words(word)
    SELECT word.x.value('.','VARCHAR(100)') AS [word]
    FROM cteSplitV2 
    CROSS APPLY words.nodes('/word') AS word(x)

SELECT *
FROM @v1Words v1
JOIN @v2Words v2
    ON v1.word = v2.word

Upvotes: -1

Related Questions