如何比较SQL Server中的两个逗号分隔列表并返回列表中的两个共同的值[重复]

This question already has an answer here:

这个问题在这里已有答案：

compare comma separated values in sql 1 answer
比较sql 1中的逗号分隔值

The question above is all I need help with. I have two common separated lists and I need to compare the two lists and return the values that are in both lists.

上面的问题是我需要帮助的。我有两个常见的分隔列表，我需要比较两个列表并返回两个列表中的值。

2 个解决方案

#1

You could, for instance, parse each list into a table (variable) and then use an inner join to obtain the common values.

例如，您可以将每个列表解析为表（变量），然后使用内部联接来获取公共值。

#2

You can use the Split function from this SO post and then get the common elements using INTERSECT:

您可以使用此SO帖子中的Split功能，然后使用INTERSECT获取常用元素：

DECLARE @String1 VARCHAR(50)='A,B,C'
DECLARE @String2 VARCHAR(50)='A,B'

SELECT  * FROM dbo.Split(@String1, ',')
INTERSECT
SELECT  * FROM dbo.Split(@String2, ',')

I am referring to this Split function as originally posted by Romil Kumar Jain here.

我指的是这个分裂功能，最初由Romil Kumar Jain在这里发布。

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item                 VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

#1