[SQL] CTE를 사용하여 문자열 분할에 효율적인 방법

CTE를 사용하여 문자열 분할에 효율적인 방법

나는 외모가 좋아하는 테이블이

ID  Layout
1   hello,world,welcome,to,tsql
2   welcome,to,stackoverflow

원하는 출력은이어야

Id  Splitdata
1   hello
1   world
1   welcome
1   to
1   tsql
2   welcome
2   to
2   stackoverflow

나는 아래의 쿼리에 의해 이런 짓을했는지

Declare @t TABLE(
    ID  INT IDENTITY PRIMARY KEY,
    Layout VARCHAR(MAX)
)
INSERT INTO @t(Layout)
SELECT 'hello,world,welcome,to,tsql' union all
SELECT 'welcome,to,stackoverflow'
--SELECT * FROM @t
;With cte AS(
select F1.id
 ,O.splitdata 
 from
 (
 select *,
 cast('<X>'+replace(F.Layout,',','</X><X>')+'</X>' as XML) as xmlfilter
 from @t F
 )F1
 cross apply
 ( 
 select fdata.D.value('.','varchar(MAX)') as splitdata 
 from f1.xmlfilter.nodes('X') as fdata(D)) O
 )

 select * from cte

그러나 성능은 현명한 매우 나쁘다. 나는 더 효율적인 쿼리를 찾고 있지만 CTE를 사용하고 있습니다.

해결법

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

   1.당신은 CTE를 사용하여 죽은 세트를 보인다, 그래서이 시도 :

   당신은 CTE를 사용하여 죽은 세트를 보인다, 그래서이 시도 :

   DECLARE @YourTable table (RowID int, Layout varchar(200))
   INSERT @YourTable VALUES (1,'hello,world,welcome,to,tsql')
   INSERT @YourTable VALUES (2,'welcome,to,stackoverflow')
   
   ;WITH SplitSting AS
   (
       SELECT
           RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part
               ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder
           FROM @YourTable
           WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)>0
       UNION ALL
       SELECT
           RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
               ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
           FROM SplitSting
           WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
       UNION ALL
       SELECT
           RowID,Remainder,null
           FROM SplitSting
           WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
   )
   SELECT * FROM SplitSting ORDER BY RowID
   

   산출:

   RowID       Part                   
   ----------- -----------------------
   1           hello                  
   1           world                  
   1           welcome                
   1           to                     
   1           tsql                   
   2           welcome                
   2           to                     
   2           stackoverflow          
   
   (8 row(s) affected)
   

   "배열 및 목록 SQL Server 2005 및 너머에서, 표 값 매개 변수하지 않음 컷을"ERLAND Sommarskog에 의해 여기 SQL Server의 분할 문자열에 대한 훌륭한 기사입니다

   편집 여기에 또 다른 버전입니다 (하지만 당신은 숫자 테이블이 필요) 위와 같은 결과를 반환합니다 :

   ;WITH SplitValues AS
   (
       SELECT
           RowID,ListValue
           FROM (SELECT
                     RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1))) AS ListValue
                     FROM (
                              SELECT RowID, ',' + Layout + ',' AS List2
                              FROM @YourTable
                          ) AS dt
                         INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                     WHERE SUBSTRING(List2, number, 1) = ','
                ) dt2
           WHERE ListValue IS NOT NULL AND ListValue!=''
   )
   SELECT * FROM SplitValues
   

   번호 테이블 여기를 참조하십시오 번호 테이블을 작성하고 채울 수있는 가장 좋은 방법은 무엇입니까?

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

   2.그것은 CTE를 사용하여 내 최고의 솔루션입니다 :

   그것은 CTE를 사용하여 내 최고의 솔루션입니다 :

   DECLARE @Char VARCHAR(MAX) = '10||3112||||aaaa||'
   DECLARE @Separador CHAR(2) = '||'
   
   ;WITH Entrada AS(
       SELECT
           CAST(1 AS Int) As Inicio,
           CHARINDEX(@Separador, @Char) As Fim
       UNION ALL
       SELECT
           CAST(Fim + LEN(@Separador) AS Int) As Inicio,
           CHARINDEX(@Separador, @Char, Fim + 1) As Fim
       FROM Entrada
       WHERE CHARINDEX(@Separador, @Char, Fim + 1) > 0
   )
   SELECT 
       SUBSTRING(@Char, Inicio, Fim - Inicio)
   FROM Entrada
   WHERE (Fim - Inicio) > 0
   

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

   3.NullRef의 대답에서

   NullRef의 대답에서

   집합 연산을하지 않고 기능은 SQL 서버에 대한 이해에 따라 빨라집니다

   그래서 이것은 매우 효율적입니다

   CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
   RETURNS TABLE
   AS
   RETURN
       WITH a AS(
           SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
           UNION ALL
           SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
           FROM a
           WHERE idx2>0
       )
       SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
       FROM a
   

from https://stackoverflow.com/questions/6381587/efficient-way-to-string-split-using-cte by cc-by-sa and MIT license