问题描述:

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?

网友答案:

Use a split function (many examples here - CLR is going to be your best option in most cases).

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;
网友答案:

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
相关阅读:
Top