SQL表逗号分隔值包含任何变量值检查 [英] Sql table comma separated values contain any of variable values checking

问题描述

我有一个变量@a='1,2,3,4'和一个包含列B的表，该列包含用逗号分隔的值.

I have a variable @a='1,2,3,4' and a table that contain a column B that contain comma separated values.

如何检查列B的值是否包含任何@a变量值?

How can I check that column B values contain any of the @a variable values?

推荐答案

您需要实现一个用于拆分值的函数.有很多变体，您可以使用:

You need to implement a function for splitting the values. There are a lot of variations, you can use this:

CREATE FUNCTION [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder](@List nvarchar(max), @Delimiter nvarchar(10) = ',')
RETURNS @result TABLE 
(   
    [Value] nvarchar(max),
    [SortOrder] bigint NOT NULL
)
AS
BEGIN
    IF @Delimiter is null 
    BEGIN
        SET @Delimiter = ','
    END
    DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@List, @Delimiter, ']]></r><r><![CDATA[') + ']]></r>'

    DECLARE @BufTable TABLE (Value nvarchar(max), SortOrder bigint NOT NULL IDENTITY(1, 1) PRIMARY KEY)
    INSERT INTO @BufTable (Value)
        SELECT Tbl.Col.value('.', 'nvarchar(max)')
        FROM   @xml.nodes('//r') Tbl(Col)
        OPTION (OPTIMIZE FOR (@xml = NULL)) 

    INSERT INTO @result (Value, SortOrder)
        SELECT Value, SortOrder
        FROM @BufTable

    RETURN
END

具有这种功能，非常简单:

Having such function, its pretty easy:

DECLARE @DataSource TABLE
(
    [column] VARCHAR(1024)
);

DECLARE @column VARCHAR(1024) = '1,2,3,4';

INSERT INTO @DataSource ([column])
VALUES ('100,200,300')
      ,('100,1,500')
      ,('1,2,3,500')
      ,('200')
      ,('33,32,31,4,30');

SELECT DISTINCT [column]
FROM @DataSource
CROSS APPLY [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] ([column], ',') DSV
INNER JOIN [dbo].[fn_Analysis_ConvertCsvListToNVarCharTableWithOrder] (@column, ',') FV
    ON DSV.[Value] = FV.[Value];

使用CROSS APPLY我们将为每一列拆分值.然后，我们将拆分过滤值并执行INNER JOIN，以便仅匹配具有包含在过滤器值中的值的行.之后，我们需要DISTINCT，因为列值可能包含来自过滤器的许多值.

Using CROSS APPLY we are splitting the values for each column. Then we are splitting the filtering values and performing INNER JOIN in order to match only the rows having a value contained in the filter value. After that, we need a DISTINCT because column value may contains many values from the filter.

这篇关于SQL表逗号分隔值包含任何变量值检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！