복붙노트

[SQL] T-SQL 분할 문자열

SQL

T-SQL 분할 문자열

나는 쉼표 (,)로 분할해야하는 문자열을 포함하는 SQL 서버 2008 R2 열 수 있습니다. I에 유래에 많은 답변을 보았다하지만 그들 중 누구도 R2에서 작동하지 않습니다. 나는 확실히 만든 나는 어떤 분할 기능 예제에 대한 선택 권한이 있습니다. 어떤 도움을 크게 감사합니다.

해결법

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

    1.당신을 위해 작동 할 수있는 전에이 SQL을 사용했습니다 : -

    당신을 위해 작동 할 수있는 전에이 SQL을 사용했습니다 : -

    CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

    그것을 사용하기 : -

    SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
  2. ==============================

    2.대신 루프 재귀 CTE에의하는 동안, 사람이 더 설정 기반의 접근 방식으로 간주하고있다? 이 기능은 SQL Server 2008 및 구분 기호로 쉼표를 기반으로 한 질문에 대한 작성되었습니다 있습니다. SQL 서버 2016 이상에서 (그리고 호환성 수준 130 이상에), STRING_SPLIT ()는 더 나은 옵션입니다.

    대신 루프 재귀 CTE에의하는 동안, 사람이 더 설정 기반의 접근 방식으로 간주하고있다? 이 기능은 SQL Server 2008 및 구분 기호로 쉼표를 기반으로 한 질문에 대한 작성되었습니다 있습니다. SQL 서버 2016 이상에서 (그리고 호환성 수준 130 이상에), STRING_SPLIT ()는 더 나은 옵션입니다.

    CREATE FUNCTION dbo.SplitString
(
  @List     nvarchar(max),
  @Delim    nvarchar(255)
)
RETURNS TABLE
AS
  RETURN ( SELECT [Value] FROM 
  ( 
    SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
      CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
    FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
      FROM sys.all_columns) AS x WHERE Number <= LEN(@List)
      AND SUBSTRING(@Delim + @List, [Number], DATALENGTH(@Delim)/2) = @Delim
    ) AS y
  );
GO

    (SQL 서버 2017의 모델 9980; 훨씬 더 자신의 사용자 데이터베이스에서) 당신이 문자열 존재 <= sys.all_columns의 행 수의 길이 제한을 피하려면, 당신은을 유도하기위한 다른 방법을 사용할 수 있습니다 같은 번호의 자신의 테이블을 구축하는 등의 숫자. 당신이 시스템 테이블을 사용하거나 직접 만들 수있는 당신은 또한 경우에 재귀 CTE를 사용할 수 있습니다 :

    CREATE FUNCTION dbo.SplitString
(
  @List     nvarchar(max),
  @Delim    nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 
       FROM n WHERE n <= LEN(@List))
       SELECT [Value] = SUBSTRING(@List, n, 
       CHARINDEX(@Delim, @List + @Delim, n) - n)
       FROM n WHERE n <= LEN(@List)
      AND SUBSTRING(@Delim + @List, n, DATALENGTH(@Delim)/2) = @Delim
   );
GO

    하지만 당신은 100 자 <문자열에 대한 재귀와 피할 오류를 위해 (가장 긴 문자열 길이 경우 <32768> 외부 쿼리에 추가 옵션 (MAXRECURSION 0) 또는 MAXRECURSION)>로해야합니다. 그 또한 좋은 대안이 아닌 경우 코멘트에 뾰족한 아웃 등이 답변을 참조하십시오.

    (또한, 구분 기호는 NCHAR (<= 1228)해야 할 것이다. 아직 이유를 연구.)

    더 분할 기능, 왜 (그리고 증거가)에 분할 문자열은 애플리케이션 계층에서 오는 경우 루프와 재귀 CTE를하지 스케일, 그리고 더 나은 대안을 수행하는 동안 :

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

    3.마지막으로 대기는 SQL 서버에서 끝난 2016가 분할 문자열 기능을 도입했습니다 : STRING_SPLIT

    마지막으로 대기는 SQL 서버에서 끝난 2016가 분할 문자열 기능을 도입했습니다 : STRING_SPLIT

    select * From STRING_SPLIT ('a,b', ',') cs

    ... XML, 집계 테이블과 같은 분할 문자열, While 루프 등의 모든 다른 방법이 STRING_SPLIT 기능에 날아되었습니다.

    여기에 성능 비교와 훌륭한 기사입니다 : 성능 놀라움과 가정 : STRING_SPLIT

  4. ==============================

    4.이 작업을 수행하는 가장 쉬운 방법은 XML 형식을 사용하는 것입니다.

    이 작업을 수행하는 가장 쉬운 방법은 XML 형식을 사용하는 것입니다.

    1. 테이블없이 행으로 문자열 변환

    질문

    DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','    

SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

    결과

     x---------x
 | Value   |
 x---------x
 | String1 |
 | String2 |
 | String3 |
 x---------x

    CSV 각 행에 대한 ID를 가지고 테이블에서 로우로 변환 2.

    소스 테이블

     x-----x--------------------------x
 | Id  |           Value          |
 x-----x--------------------------x
 |  1  |  String1,String2,String3 |
 |  2  |  String4,String5,String6 |     
 x-----x--------------------------x

    질문

    -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','

SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value' 
FROM  
(     
     SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data            
     FROM TABLENAME
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

    결과

     x-----x----------x
 | Id  |  Value   |
 x-----x----------x
 |  1  |  String1 |
 |  1  |  String2 |  
 |  1  |  String3 |
 |  2  |  String4 |  
 |  2  |  String5 |
 |  2  |  String6 |     
 x-----x----------x
  5. ==============================

    5.나는 빠른 방법은 우편 번호에서 +4를 제거 할 필요가 있었다.

    나는 빠른 방법은 우편 번호에서 +4를 제거 할 필요가 있었다.

    UPDATE #Emails 
  SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1)) 
  WHERE ZIPCode LIKE '%-%'

    어떤 PROC 없습니다 ... 아니 UDF ... 무엇을해야합니까 하나의 꽉 작은 인라인 명령. 아니 공상, 우아한 없습니다.

    등, 필요에 따라 구분 기호를 변경, 그것은 아무것도 작동합니다.

  6. ==============================

    6.당신은 대체하는 경우

    당신은 대체하는 경우

    WHILE CHARINDEX(',', @stringToSplit) > 0

    와

    WHILE LEN(@stringToSplit) > 0

    당신은 while 루프 후 마지막 삽입을 제거 할 수 있습니다!

    CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE LEN(@stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)


if @pos = 0
        SELECT @pos = LEN(@stringToSplit)


  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 RETURN
END
  7. ==============================

    7.루프 - 보내고 (반복)의 일종을 사용하여 문자열 분할의 모든 기능이 나쁜 성능을 가지고있다. 그들은 집합 기반 솔루션으로 교체해야합니다.

    루프 - 보내고 (반복)의 일종을 사용하여 문자열 분할의 모든 기능이 나쁜 성능을 가지고있다. 그들은 집합 기반 솔루션으로 교체해야합니다.

    이 코드는 우수한 실행합니다.

    CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO
  8. ==============================

    8.나는 최근에 이런 식으로 뭔가를 써야했다. 여기에 내가 생각 해낸 해결책이다. 그것은 어떤 구분 문자열을 일반화하고 나는 그것이 약간 더 나은 수행하는 것이라고 생각 것 :

    나는 최근에 이런 식으로 뭔가를 써야했다. 여기에 내가 생각 해낸 해결책이다. 그것은 어떤 구분 문자열을 일반화하고 나는 그것이 약간 더 나은 수행하는 것이라고 생각 것 :

    CREATE FUNCTION [dbo].[SplitString] 
    ( @string nvarchar(4000)
    , @delim nvarchar(100) )
RETURNS
    @result TABLE 
        ( [Value] nvarchar(4000) NOT NULL
        , [Index] int NOT NULL )
AS
BEGIN
    DECLARE @str nvarchar(4000)
          , @pos int 
          , @prv int = 1

    SELECT @pos = CHARINDEX(@delim, @string)
    WHILE @pos > 0
    BEGIN
        SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
        INSERT INTO @result SELECT @str, @prv

        SELECT @prv = @pos + LEN(@delim)
             , @pos = CHARINDEX(@delim, @string, @pos + 1)
    END

    INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
    RETURN
END
  9. ==============================

    9.금지 된 문자의 경우 XML 요소 휴식과 함께 자주 사용되는 방법. 이것은 심지어 구분 기호로 세미콜론 (;)으로, 성격의 종류이 방법을 사용하는 방법입니다.

    금지 된 문자의 경우 XML 요소 휴식과 함께 자주 사용되는 방법. 이것은 심지어 구분 기호로 세미콜론 (;)으로, 성격의 종류이 방법을 사용하는 방법입니다.

    비결은 첫째 올바르게 이스케이프 모든 금지 문자를 얻기 위해 XML 경로에 대한 SELECT SomeString AS [*]를 ( '')를 사용하는 것이다. 그게 내가 가진 문제를 피하기 위해 마법의 값으로 구분 기호를 교체 이유,이다; 구분 기호로.

    DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX))
INSERT INTO @Dummy VALUES
 (1,N'A&B;C;D;E, F')
,(2,N'"C" & ''D'';<C>;D;E, F');

DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")!

WITH Casted AS
(
    SELECT *
          ,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe
    FROM @Dummy
)
SELECT Casted.ID
      ,x.value(N'.',N'nvarchar(max)') AS Part 
FROM Casted
CROSS APPLY SplitMe.nodes(N'/x') AS A(x)

    결과

    ID  Part
1   A&B
1   C
1   D
1   E, F
2   "C" & 'D'
2   <C>
2   D
2   E, F
  10. ==============================

    10.누군가가 필요합니다 경우 CTE를 사용하는 솔루션 (내가 쓴 이유를 분명히했던 나를 떠나서는, 즉).

    누군가가 필요합니다 경우 CTE를 사용하는 솔루션 (내가 쓴 이유를 분명히했던 나를 떠나서는, 즉).

    declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';

with StringToSplit as (
  select 
      ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
    , substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail

  union all

  select
      ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
    , substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) > 0

  union all

  select
      ltrim( rtrim( Tail ) ) Head
    , '' Tail
  from StringToSplit
  where charindex( @SplitChar, Tail ) = 0
    and len( Tail ) > 0
)
select Head from StringToSplit
  11. ==============================

    11.이것은 더 좁게 맞춤형이다. 나는이 작업을 수행 할 때 나는 보통 내가 INT 또는 BIGINT의 기본 키가 다른 테이블에 조인 내부로 사용할 테이블로 캐스팅하려는 고유 ID (INT 또는 BIGINT)의 쉼표로 구분 된 목록을 가지고있다. 나는 인라인 테이블 반환 함수 내가 가장 효율적인 가능 합류하게되어 너무 반환합니다.

    이것은 더 좁게 맞춤형이다. 나는이 작업을 수행 할 때 나는 보통 내가 INT 또는 BIGINT의 기본 키가 다른 테이블에 조인 내부로 사용할 테이블로 캐스팅하려는 고유 ID (INT 또는 BIGINT)의 쉼표로 구분 된 목록을 가지고있다. 나는 인라인 테이블 반환 함수 내가 가장 효율적인 가능 합류하게되어 너무 반환합니다.

    샘플 사용은 다음과 같습니다

     DECLARE @IDs VARCHAR(1000);
 SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
 SELECT me.Value
 FROM dbo.MyEnum me
 INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID

    나는 수를 변경 http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html에서 아이디어를 훔친 인라인 테이블 값과 INT로 캐스팅.

    create function dbo.GetIntIDTableFromDelimitedString
    (
    @IDs VARCHAR(1000)  --this parameter must start and end with a comma, eg ',123,456,'
                        --all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
 RETURN

SELECT
    CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID 
FROM   
     [master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P' 
AND    Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND    SUBSTRING(@IDs,Nums.number,1) = ','
AND    CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;

GO
  12. ==============================

    12.이 여기에 올바른 버전입니다하지만 난 그들이 그렇게하지 함수로하지만, 코드의 더 큰 조각의 일부로 사용할 수 있도록 아니라 그것을 만드는 등 뒤에 쉼표를해야하는 경우에 약간의 내결함성을 추가 할 수있는 좋은 것이라고 생각 . 다만 경우에 당신은 단지 시간이되면 그것을 사용하고 있고 함수가 필요하지 않습니다. 이것은 당신이 당신의 데이터 유형을 변경해야 할 수도 있습니다, 그래서 (나는 그것을 필요로 무엇 인) 정수에 대한도.

    이 여기에 올바른 버전입니다하지만 난 그들이 그렇게하지 함수로하지만, 코드의 더 큰 조각의 일부로 사용할 수 있도록 아니라 그것을 만드는 등 뒤에 쉼표를해야하는 경우에 약간의 내결함성을 추가 할 수있는 좋은 것이라고 생각 . 다만 경우에 당신은 단지 시간이되면 그것을 사용하고 있고 함수가 필요하지 않습니다. 이것은 당신이 당신의 데이터 유형을 변경해야 할 수도 있습니다, 그래서 (나는 그것을 필요로 무엇 인) 정수에 대한도.

    DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'

--SELECT @StringToSeperate IDs INTO #Test

DROP TABLE #IDs
CREATE TABLE #IDs (ID int) 

DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)

--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
    SELECT @Position  = CHARINDEX(',', @StringToSeperate)  
    SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)

    INSERT INTO #IDs 
    SELECT @CommaSeperatedValue

    SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)

END

--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
    INSERT INTO #IDs
    SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END

SELECT * FROM #IDs
  13. ==============================

    13.나는 + 앤디 로빈슨의 기능을 조금 수정했습니다. 지금 당신은 반환 테이블에만 필요한 부분을 선택할 수 있습니다 :

    나는 + 앤디 로빈슨의 기능을 조금 수정했습니다. 지금 당신은 반환 테이블에만 필요한 부분을 선택할 수 있습니다 :

    CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )

RETURNS

 @returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN

 DECLARE @name NVARCHAR(255)

 DECLARE @pos INT

 DECLARE @orderNum INT

 SET @orderNum=0

 WHILE CHARINDEX('.', @stringToSplit) > 0

 BEGIN
    SELECT @orderNum=@orderNum+1;
  SELECT @pos  = CHARINDEX('.', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @orderNum,@name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END
    SELECT @orderNum=@orderNum+1;
 INSERT INTO @returnList
 SELECT @orderNum, @stringToSplit

 RETURN
END


Usage:

    SELECT 이름 ( 'ELIS.YD.CRP1.1.CBA.MDSP.T389.BT')를 dbo.splitstring FROM WHERE numOrder = 5

  14. ==============================

    14.당신이 최소한의 코드와 일반적인 경우에 대한 빠른 임시 솔루션을 필요로하는 경우,이 재귀 CTE 두 라이너는 그것을 할 것입니다 :

    당신이 최소한의 코드와 일반적인 경우에 대한 빠른 임시 솔루션을 필요로하는 경우,이 재귀 CTE 두 라이너는 그것을 할 것입니다 :

    DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,'

;WITH
a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i),
b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b

    어느 독립형 진술로 사용하거나 쿼리 중 하나에 위의 열팽창 계수를 추가하고 당신은 더 표현에 사용하기 위해 다른 사람들과 결과 표 B에 가입 할 수 있습니다.

    당신이 카운터를 추가하는 경우에는 목록과 함께 위치 인덱스를 얻을 것이다 :

    DECLARE @s VARCHAR(200) = '1,2333,344,4'

;WITH
a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i),
b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0)
SELECT * FROM b;

    결과:

    n   s
1   1
2   2333
3   344
4   4
  15. ==============================

    15.나는 요소에 값을 래핑 (M 아무것도하지만, 작품)에 의해 XML 경로를 가지고 :

    나는 요소에 값을 래핑 (M 아무것도하지만, 작품)에 의해 XML 경로를 가지고 :

    declare @v nvarchar(max) = '100,201,abcde'

select 
    a.value('.', 'varchar(max)')
from
    (select cast('<M>' + REPLACE(@v, ',', '</M><M>') + '</M>' AS XML) as col) as A
    CROSS APPLY A.col.nodes ('/M') AS Split(a)
  16. ==============================

    16.여기 PATINDEX, 상기 포스트의 단순한 적응을 사용하여 패턴을 분리 할 수있는 버전이다. 나는 여러 분리 문자가 포함 된 문자열을 분할하는 데 필요한 경우가 있었다.

    여기 PATINDEX, 상기 포스트의 단순한 적응을 사용하여 패턴을 분리 할 수있는 버전이다. 나는 여러 분리 문자가 포함 된 문자열을 분할하는 데 필요한 경우가 있었다.

    
alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = PATINDEX(@splitPattern, @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');

    이 같은 결과 외모

    stringa stringb 엑스 Y 부터

  17. ==============================

    17.개인적으로 나는이 기능을 사용 :

    개인적으로 나는이 기능을 사용 :

    ALTER FUNCTION [dbo].[CUST_SplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1) 
        FROM Split
        WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
  18. ==============================

    18.여기에 요청에 따라 나는 (두 개의 분리 문자 이동합니다) 이중 분배기를 개발했다. 보는이 스레드에서 어떤 가치가있을 수는 가장 문자열 분할에 관한 질의에 대한 참조.

    여기에 요청에 따라 나는 (두 개의 분리 문자 이동합니다) 이중 분배기를 개발했다. 보는이 스레드에서 어떤 가치가있을 수는 가장 문자열 분할에 관한 질의에 대한 참조.

    CREATE FUNCTION uft_DoubleSplitter 
(   
    -- Add the parameters for the function here
    @String VARCHAR(4000), 
    @Splitter1 CHAR,
    @Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
                   SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
                   MId INT,
                   SValue VARCHAR(4000))
SET @String = @String+@Splitter1

WHILE CHARINDEX(@Splitter1, @String) > 0
    BEGIN
       DECLARE @WorkingString VARCHAR(4000) = NULL

       SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
       --Print @workingString

       INSERT INTO @FResult
       SELECT CASE
            WHEN @WorkingString = '' THEN NULL
            ELSE @WorkingString
            END

       SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))

    END
IF ISNULL(@Splitter2, '') != ''
    BEGIN
       DECLARE @OStartLoop INT
       DECLARE @OEndLoop INT

       SELECT @OStartLoop = MIN(Id),
            @OEndLoop = MAX(Id)
       FROM @FResult

       WHILE @OStartLoop <= @OEndLoop
          BEGIN
             DECLARE @iString VARCHAR(4000)
             DECLARE @iMId INT

             SELECT @iString = SValue+@Splitter2,
                   @iMId = Id
             FROM @FResult
             WHERE Id = @OStartLoop

             WHILE CHARINDEX(@Splitter2, @iString) > 0
                BEGIN
                    DECLARE @iWorkingString VARCHAR(4000) = NULL

                    SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)

                    INSERT INTO @SResult
                    SELECT @iMId,
                         CASE
                         WHEN @iWorkingString = '' THEN NULL
                         ELSE @iWorkingString
                         END

                    SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))

                END

             SET @OStartLoop = @OStartLoop + 1
          END
       INSERT INTO @Result
       SELECT MId AS PrimarySplitID,
            ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
            SValue
       FROM @SResult
    END
ELSE
    BEGIN
       INSERT INTO @Result
       SELECT Id AS PrimarySplitID,
            NULL AS SecondarySplitID,
            SValue
       FROM @FResult
    END
RETURN

    용법:

    --FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)

--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

    가능한 사용 (각 분할의 두 번째 값을 가져옵니다)

    SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2
  19. ==============================

    19.다음은 함수로 사용하거나 또한이 과정에서 같은 논리를 넣을 수 있습니다 예입니다. [DBO] .fn_SplitString에서 --select *;

    다음은 함수로 사용하거나 또한이 과정에서 같은 논리를 넣을 수 있습니다 예입니다. [DBO] .fn_SplitString에서 --select *;

    CREATE FUNCTION [dbo].[fn_SplitString]
(@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',')
       RETURNS @retTable TABLE 
(

    [value] VARCHAR(MAX) NULL
)AS

BEGIN

DECLARE
       @vCSV VARCHAR (MAX) = @CSV,
       @vDelimeter VARCHAR (100) = @Delimeter;

IF @vDelimeter = ';'
BEGIN
    SET @vCSV = REPLACE(@vCSV, ';', '~!~#~');
    SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~');
END;

SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&amp;'), '<', '&lt;'), '>', '&gt;'), '''', '&apos;'), '"', '&quot;');

DECLARE @xml XML;

SET @xml = '<i>' + REPLACE(@vCSV, @vDelimeter, '</i><i>') + '</i>';

INSERT INTO @retTable
SELECT
       x.i.value('.', 'varchar(max)') AS COLUMNNAME
  FROM @xml.nodes('//i')AS x(i);

 RETURN;
END;
  20. ==============================

    20.재귀 CTE 기반 솔루션

    재귀 CTE 기반 솔루션

    declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
       ('ROOT/South America/Lima/Test/Test2')
     , ('ROOT/South America/Peru/Test/Test2')
     , ('ROOT//South America/Venuzuala ')
     , ('RtT/South America / ') 
     , ('ROOT/South Americas// '); 
declare @split char(1) = '/';
select @split as split;
with cte as 
(  select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end  as col1, 0 as pos                             , 1 as cnt
   from @T t
   union all 
   select t.iden, t.col1                                                                                                                              , charindex(@split, t.col1, t.pos + 1), cnt + 1 
   from cte t 
   where charindex(@split, t.col1, t.pos + 1) > 0 
)
select t1.*, t2.pos, t2.cnt
     , ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1 
join cte t2 
  on t2.iden = t1.iden 
 and t2.cnt  = t1.cnt+1
 and t2.pos > t1.pos 
order by t1.iden, t1.cnt;
  21. ==============================

    21.이것은 앤디 로버트슨의 답변에 따라, 나는 쉼표 이외의 구분 기호를 필요로했다.

    이것은 앤디 로버트슨의 답변에 따라, 나는 쉼표 이외의 구분 기호를 필요로했다.

    CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max))
RETURNS
 @returnList TABLE ([value] [nvarchar] (MAX))
AS
BEGIN

 DECLARE @value NVARCHAR(max)
 DECLARE @pos INT

 WHILE CHARINDEX(@delim, @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(@delim, @stringToSplit)  
  SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1)

  INSERT INTO @returnList 
  SELECT @value

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
GO

    그리고 그것을 사용합니다 :

    SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');

    (SQL 서버 2008 R2에서 테스트)

    편집 : 올바른 테스트 코드

  22. ==============================

    22./*

    /*

    
*/

    CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER
        @stringToSplit NVARCHAR(MAX)
    ) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX))
    AS BEGIN
        DECLARE @name NVARCHAR(MAX)
        DECLARE @pos BIGINT
        SET @stringToSplit = @stringToSplit + ','             -- this should allow entries that end with a `,` to have a blank value in that "column"
        WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN           -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above
            SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value
            SET @name = SUBSTRING(@stringToSplit, 1, @pos-1)  --MAX size of string of type nvarchar is 4000 
            SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned."
            INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added
            -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')'
        END
        RETURN
    END
    GO

/*

    테스트 케이스 : URL은 "향상된 기능"위와 같이 참조 참조

    SELECT * LEN (아이템 + '_') - 1 splitstring에서 'L'( '은 ,, B')

    Item | L
---  | ---
a    | 1
     | 0
b    | 1

    SELECT * LEN (아이템 + '_') - 1 'L'에서 splitstring ( '는 ,,')

    Item | L   
---  | ---
a    | 1
     | 0
     | 0

    SELECT * LEN (아이템 + '_') - 1 'L'에서 splitstring ( '는 ,,')

    Item | L   
---  | ---
a    | 1
     | 0
     | 1

    splitstring 1 'L'( '은 ,, C') - SELECT * LEN (아이템 + '_')

    Item | L   
---  | ---
a    | 1
     | 0
 c   | 3

    */

  23. ==============================

    23.

    ALTER FUNCTION [dbo].func_split_string
(
    @input as varchar(max),
    @delimiter as varchar(10) = ";"

)
RETURNS @result TABLE
(
    id smallint identity(1,1),
    csv_value varchar(max) not null
)
AS
BEGIN
    DECLARE @pos AS INT;
    DECLARE @string AS VARCHAR(MAX) = '';

    WHILE LEN(@input) > 0
    BEGIN           
        SELECT @pos = CHARINDEX(@delimiter,@input);

        IF(@pos<=0)
            select @pos = len(@input)

        IF(@pos <> LEN(@input))
            SELECT @string = SUBSTRING(@input, 1, @pos-1);
        ELSE
            SELECT @string = SUBSTRING(@input, 1, @pos);

        INSERT INTO @result SELECT @string

        SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)       
    END
    RETURN  
END
  24. ==============================

    24.이 기능을 사용할 수 있습니다 :

    이 기능을 사용할 수 있습니다 :

            CREATE FUNCTION SplitString
        (    
           @Input NVARCHAR(MAX),
           @Character CHAR(1)
          )
            RETURNS @Output TABLE (
            Item NVARCHAR(1000)
          )
        AS
        BEGIN

      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO
  25. ==============================

    25.@AviG에 외람된이 전체의 모든 토큰을 반환 그에 의해 deviced 기능의 버그 무료 버전입니다.

    @AviG에 외람된이 전체의 모든 토큰을 반환 그에 의해 deviced 기능의 버그 무료 버전입니다.

    IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString')
DROP FUNCTION [dbo].[TF_SplitString]
GO

-- =============================================
-- Author:  AviG
-- Amendments:  Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe
-- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results
-- Usage
-- select * from   [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',')
-- 969 items should be returned
-- select * from   [dbo].[TF_SplitString]('4672978261,4672978255',',')
-- 2 items should be returned
-- =============================================
CREATE FUNCTION dbo.TF_SplitString 
( @stringToSplit VARCHAR(MAX) ,
  @delimeter char = ','
)
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

    DECLARE @name NVARCHAR(255)
    DECLARE @pos INT

    WHILE LEN(@stringToSplit) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(@delimeter, @stringToSplit)


        if @pos = 0
        BEGIN
            SELECT @pos = LEN(@stringToSplit)
            SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)  
        END
        else 
        BEGIN
            SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
        END

        INSERT INTO @returnList 
        SELECT @name

        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
    END

 RETURN
END
  26. ==============================

    26.가장 쉬운 방법 :

    가장 쉬운 방법 :

    심지어 명시 버전에서 작동합니다 :).

    27. from https://stackoverflow.com/questions/10914576/t-sql-split-string by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL은 - 열을 행  (0) 2020.03.05
[SQL] 내부와 업데이트 문은 오라클에 합류  (0) 2020.03.05
[SQL] SQL Server의 누적 합계를 계산  (0) 2020.03.05
[SQL] 어떻게 업데이트는 SQL Server에서 SELECT에서합니까?  (0) 2020.03.05
[SQL] SQL 주입을 방지하는 좋은 방법은 무엇입니까? [복제]  (0) 2020.03.05
,

티스토리툴바