如何比较两个逗号分隔的字符串，如果至少有一个匹配，则返回TRUE [英] 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'

将返回TRUE，如果至少有一个匹配。因此，在上面的例子中，它将返回TRUE，因为值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.

在SQL中是否可能？

推荐答案

使用拆分函数（很多例子在这里 - CLR将是你最好的选择在大多数情况下）。

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;

这篇关于如何比较两个逗号分隔的字符串，如果至少有一个匹配，则返回TRUE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！