구분 기호 기반 T-SQL 분할 문자열

구분 기호 기반 T-SQL 분할 문자열

존재할 수도 있고 없을 수도 있는 구분 기호를 기준으로 분할할 데이터가 있습니다.

예제 데이터:

John/Smith
Jane/Doe
Steve
Bob/Johnson

다음 코드를 사용하여 데이터를 이름과 성으로 분할합니다.

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable

원하는 결과:

FirstName---LastName
John--------Smith
Jane--------Doe
Steve-------NULL
Bob---------Johnson

이 코드는 모든 행에 예상 구분 기호가 있는 한 작동하지만 행에 다음과 같은 구분 기호가 없는 경우 오류가 발생합니다.

"Invalid length parameter passed to the LEFT or SUBSTRING function."

제대로 작동하려면 어떻게 다시 써야 합니까?

이것이 당신에게 도움이 될 수도 있습니다.

SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn)
            ELSE CHARINDEX('/', myColumn) - 1
            END) AS FirstName
    ,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
            WHEN 0
                THEN LEN(myColumn) + 1
            ELSE CHARINDEX('/', myColumn) + 1
            END, 1000) AS LastName
FROM MyTable

SQL Server 2016+에 대한 해답을 찾는 고객을 위한 솔루션입니다.기본 제공 STRING_SPLIT 함수 사용

예:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  

SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';  

참조: https://msdn.microsoft.com/en-nz/library/mt684588.aspx

구분 기호를 사용하여 문자열이 포함된 행을 필터링하고 다음과 같은 행에만 작업합니다.

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE CHARINDEX('/', myColumn) > 0

또는

SELECT SUBSTRING(myColumn, 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn, CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM   MyTable
WHERE myColumn LIKE '%/%'

대소문자CHARINDEX('/', myColumn, 0) = 0THEN 내 칼럼ELSE LEFT(myColumn, CHARINDEX('/', myColumn, 0)-1)END AS 이름,사례.CHARINDEX('/', myColumn, 0) = 0그 때 "ELSE RIGHT(myColumn, CHARINDEX('/), REVERSE(myColumn), 0)-1)END AS 성내 테이블에서

ALTER FUNCTION [dbo].[split_string](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

2016년에 SQL Server 버전을 사용하는 경우를 대비하여 여러 구분 기호로 문자열을 분할하는 다른 방법을 제공하고자 합니다.

일반적인 아이디어는 문자열의 모든 문자를 분할하고 구분 기호의 위치를 결정한 다음 구분 기호와 관련된 하위 문자열을 얻는 것입니다.다음은 샘플입니다.

-- Sample data
DECLARE @testTable TABLE (
    TestString      VARCHAR(50)
)
INSERT INTO @testTable VALUES 
    ('Teststring,1,2,3')
    ,('Test')

DECLARE @delimiter VARCHAR(1) = ','

-- Generate numbers with which we can enumerate
;WITH Numbers AS (
    SELECT 1 AS N

    UNION ALL 

    SELECT N + 1
    FROM Numbers 
    WHERE N < 255
), 
-- Enumerate letters in the string and select only the delimiters
Letters AS (
    SELECT  n.N
            , SUBSTRING(t.TestString, n.N, 1) AS Letter
            , t.TestString 
            , ROW_NUMBER() OVER (   PARTITION BY t.TestString
                                    ORDER BY n.N
                                ) AS Delimiter_Number 
    FROM Numbers n
        INNER JOIN @testTable t
            ON n <= LEN(t.TestString)
    WHERE SUBSTRING(t.TestString, n, 1) = @delimiter 

    UNION 

    -- Include 0th position to "delimit" the start of the string
    SELECT  0
            , NULL
            , t.TestString 
            , 0
    FROM @testTable t 
)
-- Obtain substrings based on delimiter positions
SELECT  t.TestString 
        , ds.Delimiter_Number + 1 AS Position
        , SUBSTRING(t.TestString, ds.N + 1, ISNULL(de.N, LEN(t.TestString) + 1) - ds.N - 1) AS Delimited_Substring 
FROM @testTable t
    LEFT JOIN Letters ds
        ON t.TestString = ds.TestString 
    LEFT JOIN Letters de
        ON t.TestString = de.TestString 
        AND ds.Delimiter_Number + 1 = de.Delimiter_Number  
OPTION (MAXRECURSION 0)

위의 예제는 구분 기호가 하나만 있을 때는 잘 작동하지만 여러 구분 기호에 대해서는 잘 확장되지 않습니다.이 기능은 SQL Server 2016 이상에서만 사용할 수 있습니다.

/*Some Sample Data*/
DECLARE @mytable TABLE ([id] VARCHAR(10), [name] VARCHAR(1000));
INSERT INTO @mytable
VALUES ('1','John/Smith'),('2','Jane/Doe'), ('3','Steve'), ('4','Bob/Johnson')


/*Split based on delimeter*/
SELECT P.id, [1] 'FirstName', [2] 'LastName', [3] 'Col3', [4] 'Col4'
FROM(
    SELECT A.id, X1.VALUE, ROW_NUMBER() OVER (PARTITION BY A.id ORDER BY A.id) RN
    FROM @mytable A
    CROSS APPLY STRING_SPLIT(A.name, '/') X1
    ) A
PIVOT (MAX(A.[VALUE]) FOR A.RN IN ([1],[2],[3],[4],[5])) P

이 모든 것들이 제가 이것에 도달하는 데 도움이 되었습니다.나는 아직 2012년이지만 이제 문자열이 다양한 구분자 수를 가지더라도 문자열을 분할하고 해당 문자열에서 n번째 하위 문자열을 가져올 수 있는 빠른 것을 가지고 있습니다.빠르기도 합니다.이 게시물이 오래된 것은 알지만, 무언가를 찾는 데 오랜 시간이 걸렸기 때문에 이것이 다른 사람에게 도움이 되기를 바랍니다.

CREATE FUNCTION [dbo].[SplitsByIndex]
(@separator VARCHAR(20)  = ' ', 
 @string    VARCHAR(MAX), 
 @position  INT
)
RETURNS VARCHAR(MAX)
AS
     BEGIN
     DECLARE @results TABLE
     (id   INT IDENTITY(1, 1), 
      chrs VARCHAR(8000)
     );
     DECLARE @outResult VARCHAR(8000);
     WITH X(N)
          AS (SELECT 'Table1'
              FROM(VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) T(C)),
          Y(N)
          AS (SELECT 'Table2'
              FROM X A1, 
                   X A2, 
                   X A3, 
                   X A4, 
                   X A5, 
                   X A6, 
                   X A7, 
                   X A8), -- Up to 16^8 = 4 billion
          T(N)
          AS (SELECT TOP (ISNULL(LEN(@string), 0)) ROW_NUMBER() OVER(
                                                   ORDER BY
              (
                  SELECT NULL
              )) - 1 N
              FROM Y),
          Delim(Pos)
          AS (SELECT t.N
              FROM T
              WHERE(SUBSTRING(@string, t.N, LEN(@separator + 'x') - 1) LIKE @separator
                    OR t.N = 0)),
          Separated(value)
          AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator + 'x') - 1, LEAD(d.Pos, 1, 2147483647) OVER(
                     ORDER BY
              (
                  SELECT NULL
              ))-d.Pos - LEN(@separator))
              FROM Delim d
              WHERE @string IS NOT NULL)
          INSERT INTO @results(chrs)
                 SELECT s.value
                 FROM Separated s
                 WHERE s.value <> @separator;
     SELECT @outResult =
     (
         SELECT chrs
         FROM @results
         WHERE id = @position
     );
     RETURN @outResult;
 END;

다음과 같이 사용할 수 있습니다.

SELECT [dbo].[SplitsByIndex](' ',fieldname,2) 
from tablename

테스트 문자열에 항상 구분 기호를 추가하여 하위 문자열 작업을 보호합니다.이렇게 하면 구문 분석이 훨씬 쉬워집니다.이제 코드는 올바른 패턴을 찾는 데 의존할 수 있으며 특별한 경우에 대처할 필요가 없습니다.

SELECT SUBSTRING(myColumn + '/', 1, CHARINDEX('/', myColumn)-1) AS FirstName,
       SUBSTRING(myColumn + '/', CHARINDEX('/', myColumn) + 1, 1000) AS LastName
FROM MyTable

에지 케이스, 조건부 및 케이스를 제거합니다.항상 마지막에 추가 구분 기호를 추가하면 문제가 발생하지 않습니다.

언급URL : https://stackoverflow.com/questions/21768321/t-sql-split-string-based-on-delimiter