[sql] 항목 x에 액세스 할 수 있도록 문자열을 어떻게 분할합니까?

이 질문은 문자열 분할 방식 이 아니라 n 번째 요소를 얻는 방법에 관한 것 입니다.

여기에 모든 답이 재귀를 사용하여 문자열을 분할 어떤 종류의 일을하는, CTE이야, 배수 CHARINDEX, REVERSE그리고 PATINDEX, 발명 기능은 CLR 방법, 번호 테이블에 대한 호출 CROSS APPLY로 … 대부분의 답변은 많은 코드를 다룹니다.

그러나 -n 번째 요소를 얻는 접근법 이상 을 정말로 원한다면 -이것은 실제로 하나의 라이너로 , UDF는없고, 하위 선택조차도 할 수 없으며 … 추가 혜택 : type safe

공백으로 구분 된 2 부 얻기 :

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

물론 구분자 및 위치에 변수 를 사용할 수 있습니다 ( sql:column쿼리 값에서 위치를 직접 검색하는 데 사용 ).

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

문자열에 금지 된 문자 (특히 중 하나 &><) 가 포함될 수 있으면 여전히 이런 식으로 수행 할 수 있습니다. FOR XML PATH문자열에서 먼저 사용 하여 모든 금지 된 문자를 피팅 이스케이프 시퀀스로 암시 적으로 바꿉니다.

추가로 구분자가 세미콜론 인 경우 매우 특별한 경우 입니다. 이 경우 구분 기호를 먼저 ‘# DLMT #’로 바꾸고 마지막으로 XML 태그로 바꿉니다.

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

SQL-Server 2016 이상 업데이트

유감스럽게도 개발자는로 부품 색인을 반환하는 것을 잊었습니다 STRING_SPLIT. 그러나 SQL-Server 2016+를 사용하면 JSON_VALUE및이 OPENJSON있습니다.

함께 JSON_VALUE우리는 인덱스 ‘배열과 위치에 전달할 수있다.

대한 문서가 명확하게 진술한다 :OPENJSON

OPENJSON이 JSON 배열을 구문 분석 할 때이 함수는 JSON 텍스트의 요소 색인을 키로 리턴합니다.

같은 문자열은 1,2,3대괄호 만 필요합니다 : [1,2,3].
같은 단어의 문자열은 this is an example이어야 ["this","is","an","example"]합니다.
이것들은 매우 쉬운 문자열 연산입니다. 그냥 사용해보십시오.

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

-위치 안전 스트링 스플리터 ( 0부터 시작 )는 다음을 참조하십시오.

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

에서 이 게시물에 나는 다양한 접근 방법을 테스트하고, 발견 OPENJSON정말 빠릅니다. 유명한 “delimitedSplit8k ()”메소드보다 훨씬 빠릅니다.

업데이트 2-유형 안전 값 가져 오기

doubled를 사용하여 배열 내에서 배열을 사용할 수 있습니다 [[]]. 이것은 유형이 지정된 WITH-clause를 허용합니다 .

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray

답변

CREATE FUNCTION SplitString
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN
END
GO

Select * From SplitString('Hello John Smith',' ')

select * From SplitString('Hello**John**Smith','**')