我有像'2,4,5'这样的行业 ID ...并希望显示与这些行业 ID 同化的结果。SQL 服务器

Question

我有IndustryID像'2,4,5'…并希望显示与这些IndustryID相关的结果。但是在细节表中它存储的值是逗号分隔的2,4,5。因此，使用IN运算符只能使IndustryID等于2或4或5。

这是我的查询

Declare @IndustryID varchar(50)
set @IndustryID='2,4,5'
select IndustryID,Industry from DetailsMaster 
where (Industry IN (SELECT * from fnList2Table (@IndustryID))))

fnList2Table是这里的函数:

ALTER  FUNCTION [dbo].[fnList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item varchar(MAX)
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int
    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)
    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END
    RETURN
END

请给出答案

Answer 1

使用这个UDF，它将逗号分隔的字符串转换为行值，并在查询中使用它，如——in (select dbo.udf_splitStringToRows(@IndustryID))

CREATE FUNCTION dbo.udf_List2Table
(
  @List VARCHAR(MAX),
  @Delim CHAR
)
RETURNS @ParsedList TABLE
(
  item VARCHAR(MAX)
)
AS
BEGIN
  DECLARE @item VARCHAR(MAX), @Pos INT
  SET @List = LTRIM(RTRIM(@List))+ @Delim
  SET @Pos = CHARINDEX(@Delim, @List, 1)
  WHILE @Pos > 0 BEGIN
    SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
    IF @item <> '' BEGIN
      INSERT INTO @ParsedList (item)
        VALUES (CAST(@item AS VARCHAR(MAX)))
    END
    SET @List = RIGHT(@List, LEN(@List) - @Pos)
    SET @Pos = CHARINDEX(@Delim, @List, 1)
  END
  RETURN
END
GO