복붙노트

[SQL] SQL 서버 2012 년 STRING_SPLIT

SQL

SQL 서버 2012 년 STRING_SPLIT

나는이 매개 변수가

@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' 

나는 쉼표로 구분 된 값을 분할 무언가를하고 싶다.

string_split 기능이 작동하지 않습니다와 나는이 오류가 발생합니다 :

그리고 내 데이터베이스를 변경하고 130 호환성을 설정하려고하지만이 변화에 대한 권한이 없습니다.

해결법

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

    1.다른 접근 방법은 CROSS이 쉼표 데이터로 분리 된 분할 적용으로 XML 방법을 사용하여 너무 :

    다른 접근 방법은 CROSS이 쉼표 데이터로 분리 된 분할 적용으로 XML 방법을 사용하여 너무 :

    SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
    FROM
    (
        SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
    ) AS A
    CROSS APPLY String.nodes('/X') AS Split(a);
    

    결과 :

    DATA
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    

    예 :

    DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
    DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
    DECLARE @StudentsMark TABLE
    (id    NVARCHAR(300),
     marks NVARCHAR(300)
    ); 
    --insert into @StudentsMark 
    ;WITH CTE
         AS (
         SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
                ROW_NUMBER() OVER(ORDER BY
                                 (
                                     SELECT NULL
                                 )) RN
         FROM
         (
             SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
         ) AS A
         CROSS APPLY String.nodes('/X') AS Split(a)),
         CTE1
         AS (
         SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
                ROW_NUMBER() OVER(ORDER BY
                                 (
                                     SELECT NULL
                                 )) RN
         FROM
         (
             SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
         ) AS A
         CROSS APPLY String.nodes('/X') AS Split(a))
         INSERT INTO @StudentsMark
                SELECT C.id,
                       C1.marks
                FROM CTE C
                     LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
    SELECT *
    FROM @StudentsMark;
    
  2. ==============================

    2.인 Yogesh 샤르마 및 살만 응답에 따라 인라인 기능 :

    인 Yogesh 샤르마 및 살만 응답에 따라 인라인 기능 :

    Create FUNCTION [dbo].[fn_split_string]
    (
        @string    nvarchar(max),
        @delimiter nvarchar(max)
    )
    /*
        The same as STRING_SPLIT for compatibility level < 130
        https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
    */
    RETURNS TABLE AS RETURN
    (
        SELECT 
          --ROW_NUMBER ( ) over(order by (select 0))                            AS id     --  intuitive, but not correect
            Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
          , Split.a.value('.', 'NVARCHAR(MAX)')                                 AS value
        FROM
        (
            SELECT CAST('<X>'+REPLACE(@string, @delimiter, '</X><X>')+'</X>' AS XML) AS String
        ) AS a
        CROSS APPLY String.nodes('/X') AS Split(a)
    )
    

    예:

    DECLARE @ID NVARCHAR(300)= 'abc,d,e,f,g';
    select * from fn_split_string(@ID,',')
    
    -- If you need exactly string_split functionality (without id column):
    select value from fn_split_string(@ID,',')
    
  3. ==============================

    3.또 다른 방법은 한 동안 CHARINDEX 및 문자열을 사용하는 것입니다 :

    또 다른 방법은 한 동안 CHARINDEX 및 문자열을 사용하는 것입니다 :

    DECLARE @IDs VARCHAR(500);
    DECLARE @Number VARCHAR(500);
    DECLARE @charSpliter CHAR;
    
    SET @charSpliter = ',';
    SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' + @charSpliter;
    
    WHILE CHARINDEX(@charSpliter, @IDs) > 0
    BEGIN
        SET @Number = SUBSTRING(@IDs, 0, CHARINDEX(@charSpliter, @IDs));
        SET @IDs = SUBSTRING(@IDs, CHARINDEX(@charSpliter, @IDs) + 1, LEN(@IDs));
    
        PRINT @Number;
    
    END;
    
  4. ==============================

    4.의 작은 변화는 데이터베이스의 호환성 수준을 변경할 수 없습니다 Al3x_M의 polyfill, @ : 다른 쿼리에서 나중에 사용하기 위해, 나는 값의 목록을 저장하기 위해 테이블 ​​변수를 사용합니다 :

    의 작은 변화는 데이터베이스의 호환성 수준을 변경할 수 없습니다 Al3x_M의 polyfill, @ : 다른 쿼리에서 나중에 사용하기 위해, 나는 값의 목록을 저장하기 위해 테이블 ​​변수를 사용합니다 :

    DECLARE @IDs VARCHAR(500);
    SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2a0' ;
    
    declare @list TABLE (id int);
    DECLARE @Number int, @idx int
    DECLARE @charSpliter CHAR;
    
    SET @charSpliter = ','
    SET @IDs = @IDs + @charSpliter;
    set  @idx = 0
    
    WHILE (1 = 1)
        BEGIN
            set  @idx =  CHARINDEX(@charSpliter, @IDs)
            if (@idx is NULL or @idx <= 0) break;
    
            BEGIN TRY
            SET @Number = SUBSTRING(@IDs, 0, @idx)
            SET @IDs = SUBSTRING(@IDs, @idx + 1, LEN(@IDs))
    
            insert @list select convert(int, @Number)
        END TRY  
        BEGIN CATCH 
            break
        END CATCH  
    END
    
    -- @list available for the next query...
    select * from  @list
    
  5. ==============================

    5.행으로 여러 줄의 텍스트를 변환하는 방법을 찾고있는 사람들을 위해, 여기에 답변에 따라 코드입니다 :

    행으로 여러 줄의 텍스트를 변환하는 방법을 찾고있는 사람들을 위해, 여기에 답변에 따라 코드입니다 :

    declare @text varchar(max) = 'line0
    line1
    line2'
    
    select split.a.value('.', 'nvarchar(max)') data
    from
    (
        select cast('<x>' + replace(@text, char(13) + char(10), '</x><x>') + '</x>' as xml) as string
    ) as a
    cross apply string.nodes('/x') as split(a)
    
  6. ==============================

    6.데이터베이스 호환성 수준이 130보다 낮은 경우, SQL Server는 찾아 STRING_SPLIT 기능을 실행할 수 없습니다. 다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다 :

    데이터베이스 호환성 수준이 130보다 낮은 경우, SQL Server는 찾아 STRING_SPLIT 기능을 실행할 수 없습니다. 다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다 :

    ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
    

    호환성 수준 (120)도 새로운 푸른 SQL 데이터베이스에서 기본적 될 수 있습니다.

    참고로 :

    버전 - 최고 호환성 수준 - 가장 낮은 수준

    SQL 2017-140 - (100) SQL 2016-130 - (100) SQL 2014-120 - (100) SQL 2012-110 - (90) SQL 2008 - 100-80 SQL 2005 - 90-80 SQL 2000 - 80-80

    또한,도 등 당신의 구문을 확인 :

    SELECT Value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
    
  7. ==============================

    7.나는 최종 사용자가 그들이 원하는 섹션을 선택할 수 있도록 테이블 접근 방식을 사용하여 신속하고 더러운 대신이했다. 조인 원래 문자열이 사용되거나 각 행은 스칼라 결과 선정. 테스트

    나는 최종 사용자가 그들이 원하는 섹션을 선택할 수 있도록 테이블 접근 방식을 사용하여 신속하고 더러운 대신이했다. 조인 원래 문자열이 사용되거나 각 행은 스칼라 결과 선정. 테스트

    SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; 가다

    기능의 dbo.StringSplit2012 만들기 (     @OriginalString VARCHAR (500)  세퍼레이터 VARCHAR @ (6) ) RETURNS @Sections 표 (     OriginalString VARCHAR (500) NOT NULL  , StringSection VARCHAR (500) NULL  , SectionNumber INT ) 같이     BEGIN         @SectionCount INT를 선언;

        DECLARE @LoopCounter INT = 1;
        DECLARE @RemainingString VARCHAR(500);
        DECLARE @CurrentSection VARCHAR(500);
    
    
        SET @SectionCount =
            LEN (@OriginalString) - LEN (REPLACE (@OriginalString, @Separator, ''));
        IF @SectionCount = 0
            BEGIN
                INSERT INTO
                    @Sections
                         (
                             OriginalString
                            ,StringSection
                            ,SectionNumber
                         )
                VALUES
                         (@OriginalString -- OriginalString - varchar(500)
                         ,@OriginalString -- StringSection - varchar(500)
                         ,1                             -- SectionNumber - int
                    );
            END;
    
        ELSE
            BEGIN
                SET @RemainingString = @OriginalString;
                DECLARE @SectionStart INT;
                DECLARE @SectionLength INT;
    
                WHILE @LoopCounter <= @SectionCount
                    BEGIN
                        SET @SectionStart = 1;
                        SET @SectionLength = CHARINDEX (@Separator, @RemainingString);
    
                        SET @CurrentSection = LEFT(@RemainingString, @SectionLength - 1);
    
                        INSERT INTO
                            @Sections
                                 (
                                     OriginalString
                                    ,StringSection
                                    ,SectionNumber
                                 )
                        VALUES
                                 (@OriginalString
                                 ,@CurrentSection
                                 ,@LoopCounter  -- SectionNumber - int
                            );
    
                        SET @RemainingString =
                            RIGHT(@RemainingString, LEN (@RemainingString) - @SectionLength);
    
                        SET @LoopCounter = @LoopCounter + 1;
    
    
                    END;
                DECLARE @TotalParsedLength INT =
                (
                SELECT SUM ( LEN (s.StringSection)) FROM @Sections AS    s
                ) + @SectionCount;
                SET @CurrentSection =
                    RIGHT(@RemainingString, LEN (@OriginalString) - @TotalParsedLength);
                INSERT INTO
                    @Sections
                         (
                             OriginalString
                            ,StringSection
                            ,SectionNumber
                         )
                VALUES
                         (@OriginalString
                         ,@CurrentSection
                         ,@LoopCounter  -- SectionNumber - int
                    );
            END;
    
    
    
        RETURN;
    END;
    

    가다

    나는이 사람이 약간의 시간 절약 할 수 있기를 바랍니다. 나는 작업 단계의 명령에서 나에게 패키지 이름을 지정하기 위해 만든 기능에 STRING_SPLIT을 사용하고, 내 2012 서버로 이동하는 경우가 폭발. 그래서 난 내 자신을 썼다. (나중에처럼!)

    조이 모건

    BI / 통합 개발자 III

    아스펜 치과 관리, Inc의

    시러큐스, NY

  8. from https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 by cc-by-sa and MIT license