[SQL] 구문 분석 쉼표로 구분 된 문자열은 Where 절에서 문자열의 IN 목록을 만들려면

구문 분석 쉼표로 구분 된 문자열은 Where 절에서 문자열의 IN 목록을 만들려면

내 저장 과정은 쉼표로 구분 된 문자열 파라미터를 수신한다 :

DECLARE @Account AS VARCHAR(200)
SET @Account = 'SA,A'

나는이 문장에서 확인해야합니다 :

WHERE Account IN ('SA', 'A')

이 작업을 수행하는 가장 좋은 방법은 무엇입니까?

해결법

1. ==============================

   1.(2005 + SQLSERVER)이 기능 만들기

   (2005 + SQLSERVER)이 기능 만들기

   CREATE function [dbo].[f_split]
   (
   @param nvarchar(max), 
   @delimiter char(1)
   )
   returns @t table (val nvarchar(max), seq int)
   as
   begin
   set @param += @delimiter
   
   ;with a as
   (
   select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
   union all
   select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
   from a
   where charindex(@delimiter, @param, t + 1) > 0
   )
   insert @t
   select substring(@param, f, t - f), seq from a
   option (maxrecursion 0)
   return
   end
   

   이 문을 사용하여

   SELECT *
   FROM yourtable 
   WHERE account in (SELECT val FROM dbo.f_split(@account, ','))
   

   XML 분할에 내 분할 기능을 비교 :

   테스트 데이터 :

   select top 100000 cast(a.number as varchar(10))+','+a.type +','+ cast(a.status as varchar(9))+','+cast(b.number as varchar(10))+','+b.type +','+ cast(b.status as varchar(9)) txt into a 
   from master..spt_values a cross join master..spt_values b
   

   XML :

    SELECT count(t.c.value('.', 'VARCHAR(20)'))
    FROM (
        SELECT top 100000 x = CAST('<t>' + 
              REPLACE(txt, ',', '</t><t>') + '</t>' AS XML)
              from a
    ) a
    CROSS APPLY x.nodes('/t') t(c)
   
   Elapsed time: 1:21 seconds
   

   f_split :

   select count(*) from a cross apply clausens_base.dbo.f_split(a.txt, ',')
   
   Elapsed time: 43 seconds
   

   이것은 실행하려면 실행 변경됩니다,하지만 당신은 아이디어를 얻을

2. ==============================

   2.이거 한번 해봐 -

   이거 한번 해봐 -

   DDL :

   CREATE TABLE dbo.Table1 (
         [EmpId] INT
       , [FirstName] VARCHAR(7)
       , [LastName] VARCHAR(10)
       , [domain] VARCHAR(6)
       , [Vertical] VARCHAR(10)
       , [Account] VARCHAR(50)
       , [City] VARCHAR(50)
   )
   
   INSERT INTO dbo.Table1 ([EmpId], [FirstName], [LastName], [Vertical], [Account], [domain], [City])
   VALUES 
        (345, 'Priya', 'Palanisamy', 'DotNet', 'LS', 'Abbott', 'Chennai'),
        (346, 'Kavitha', 'Amirtharaj', 'DotNet', 'CG', 'Diageo', 'Chennai'),
        (647, 'Kala', 'Haribabu', 'DotNet', 'DotNet', 'IMS', 'Chennai')
   

   질문:

   DECLARE @Account VARCHAR(200)
   SELECT @Account = 'CG,LS'
   
   SELECT *
   FROM Table1
   WHERE [Vertical] = 'DotNet' AND (ISNULL(@Account, '') = '' OR Account IN (
        SELECT t.c.value('.', 'VARCHAR(20)')
        FROM (
            SELECT x = CAST('<t>' + 
                  REPLACE(@Account, ',', '</t><t>') + '</t>' AS XML)
        ) a
        CROSS APPLY x.nodes('/t') t(c)
   ))
   

   산출:

   확장 통계 :

   SSMS SET STATISTICS TIME + IO :

   XML :

   (3720 row(s) affected)
   Table 'temp'. Scan count 3, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   
    SQL Server Execution Times:
      CPU time = 187 ms,  elapsed time = 242 ms.
   

   CTE :

   (3720 row(s) affected)
   Table '#BF78F425'. Scan count 360, logical reads 360, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   Table 'temp'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
   
    SQL Server Execution Times:
      CPU time = 281 ms,  elapsed time = 335 ms.
   

3. ==============================

   3.가장 효율적인 방법은 분할 문자열을 CLR 기능을 사용하는 것입니다. 예제 및 성능 비교를 위해이 문서를 참조하십시오

   가장 효율적인 방법은 분할 문자열을 CLR 기능을 사용하는 것입니다. 예제 및 성능 비교를 위해이 문서를 참조하십시오

from https://stackoverflow.com/questions/17481479/parse-comma-separated-string-to-make-in-list-of-strings-in-the-where-clause by cc-by-sa and MIT license