복붙노트

[SQL] T-SQL에서 분할 기능과 동등한?

SQL

T-SQL에서 분할 기능과 동등한?

나는 분할에 찾고 있어요 '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ...'(쉼표로 구분) 테이블이나 테이블 변수로 .

사람이 행에서 반환하는 각각의 기능이 있습니까?

해결법

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

    1.여기에 다소 구식 솔루션입니다 :

    여기에 다소 구식 솔루션입니다 :

    /*
    Splits string into parts delimitered with specified character.
*/
CREATE FUNCTION [dbo].[SDF_SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
            @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
        set @iStart = 2
        insert into @tParts
        values( null )
    end
    else 
        set @iStart = 1
    while 1=1
    begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
            set @iPos = len( @sString )+1
        if @iPos - @iStart > 0          
            insert into @tParts
            values  ( substring( @sString, @iStart, @iPos-@iStart ))
        else
            insert into @tParts
            values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
            break
    end
    RETURN

END

    SQL 서버 2008에서는 .NET 코드와 같은 얻을 수 있습니다. 어쩌면 더 빨리 작동,하지만 확실히이 방법은 쉽게 관리 할 수 ​​있습니다.

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

    2.이 시도

    이 시도

    DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)

    또는

    DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
    FROM CTE
    WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter) 
    THEN b - a - LEN(@delimiter) 
    ELSE LEN(@str) - a + 1 END) value      
FROM cte WHERE a > 0

    같은 일을 더 많은 방법이 쉼표로 구분 된 문자열을 분할하는 방법 여기에있다?

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

    3.당신은 STRING_SPLIT에 대해 알고 싶은 것 것 (SQL Server를 2016+ 사용)이 질문이 SQL 서버 2008하지만 미래의 방문자를 태그했습니다.

    당신은 STRING_SPLIT에 대해 알고 싶은 것 것 (SQL Server를 2016+ 사용)이 질문이 SQL 서버 2008하지만 미래의 방문자를 태그했습니다.

    이 새로운 내장 기능을 사용하면 지금 바로 사용할 수 있습니다

    SELECT TRY_CAST(value AS INT)
FROM   STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')

    이 기능의 일부 제한 및 성능 테스트의 일부 유망한 결과는 아론 베르이 블로그 게시물에 있습니다.

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

    4.이것은 .NET과 같은 대부분이며, 그 기능을 잘 알고있는 분들을 위해 :

    이것은 .NET과 같은 대부분이며, 그 기능을 잘 알고있는 분들을 위해 :

    CREATE FUNCTION dbo.[String.Split]
(
    @Text VARCHAR(MAX),
    @Delimiter VARCHAR(100),
    @Index INT
)
RETURNS VARCHAR(MAX)
AS BEGIN
    DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX));
    DECLARE @R VARCHAR(MAX);
    WITH CTE AS
    (
    SELECT 0 A, 1 B
    UNION ALL
    SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter))
    FROM CTE
    WHERE B > A
    )
    INSERT @A(V)
    SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE      
    FROM CTE WHERE A >0

    SELECT      @R
    =           V
    FROM        @A
    WHERE       ID = @Index + 1
    RETURN      @R
END

SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2'
  5. ==============================

    5.여기에 유 물었다 분할 기능입니다

    여기에 유 물었다 분할 기능입니다

    CREATE FUNCTION [dbo].[split](
          @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

    이 같은 기능을 수행

    select * from dbo.split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')
  6. ==============================

    6.

    DECLARE
    @InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
    , @delimiter varchar(10) = ','

DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)

    이 응답의 출처 : http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited

  7. ==============================

    7.나는 내가 좋아하는 솔루션에 집어 넣은 유혹하고있다. 그 결과 표 2 열 구성된다 : PosIdx을 발견 정수의 위치와; 정수의 값입니다.

    나는 내가 좋아하는 솔루션에 집어 넣은 유혹하고있다. 그 결과 표 2 열 구성된다 : PosIdx을 발견 정수의 위치와; 정수의 값입니다.

    
create function FnSplitToTableInt
(
    @param nvarchar(4000)
)
returns table as
return
    with Numbers(Number) as 
    (
        select 1 
        union all 
        select Number + 1 from Numbers where Number < 4000
    ),
    Found as
    (
        select 
            Number as PosIdx,
            convert(int, ltrim(rtrim(convert(nvarchar(4000), 
                substring(@param, Number, 
                charindex(N',' collate Latin1_General_BIN, 
                @param + N',', Number) - Number))))) as Value
        from   
            Numbers 
        where  
            Number <= len(@param)
        and substring(N',' + @param, Number, 1) = N',' collate Latin1_General_BIN
    )
    select 
        PosIdx, 
        case when isnumeric(Value) = 1 
            then convert(int, Value) 
            else convert(int, null) end as Value 
    from 
        Found

    그것은 1에서 기본적으로 100, 위치의 목록으로 재귀 CTE를 사용하여 작동합니다. 당신은 더 이상 100 이상의 문자열로 작업해야하는 경우, 단순히 다음과 같은 '옵션을 (MAXRECURSION 4000)'를 사용하여이 함수를 호출 :

    
select * from FnSplitToTableInt
(
    '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' + 
    '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
    '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
    '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
    '9, 8, 7, 6, 5, 4, 3, 2, 1, 0'
) 
option (maxrecursion 4000)
  8. ==============================

    8.

    CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

    용법

    Select * from dbo.Split(N'1,2,3,4,6',',')
  9. ==============================

    9.이 간단한의 CTE는 필요한 것을 줄 것이다 :

    이 간단한의 CTE는 필요한 것을 줄 것이다 :

    DECLARE @csv varchar(max) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15';
--append comma to the list for CTE to work correctly
SET @csv = @csv + ',';
--remove double commas (empty entries)
SET @csv = replace(@csv, ',,', ',');
WITH CteCsv AS (
    SELECT CHARINDEX(',', @csv) idx, SUBSTRING(@csv, 1, CHARINDEX(',', @csv) - 1) [Value]
    UNION ALL
    SELECT CHARINDEX(',', @csv, idx + 1), SUBSTRING(@csv, idx + 1, CHARINDEX(',', @csv, idx + 1) - idx - 1) FROM CteCsv
    WHERE CHARINDEX(',', @csv, idx + 1) > 0
)

SELECT [Value] FROM CteCsv
  10. ==============================

    10.이건 정말 어떤 제한이없는 다른 버전 (예 : 특수 문자 XML 접근, CTE 방식의 레코드 수를 사용하는 경우) 그리고 4000 희망이의 소스 문자열 평균 길이 10M + 기록에서 테스트를 기반으로 훨씬 빠르게 실행 도움을 줄 수.

    이건 정말 어떤 제한이없는 다른 버전 (예 : 특수 문자 XML 접근, CTE 방식의 레코드 수를 사용하는 경우) 그리고 4000 희망이의 소스 문자열 평균 길이 10M + 기록에서 테스트를 기반으로 훨씬 빠르게 실행 도움을 줄 수.

    Create function [dbo].[udf_split] (
    @ListString nvarchar(max),
    @Delimiter  nvarchar(1000),
    @IncludeEmpty bit) 
Returns @ListTable TABLE (ID int, ListValue nvarchar(1000))
AS
BEGIN
    Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int
    Select @ID = 1,
   @L = len(replace(@Delimiter,' ','^')),
            @ListString = @ListString + @Delimiter,
            @CurrentPosition = 1 
    Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
   While @NextPosition > 0 Begin
   Set  @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
   If      @IncludeEmpty=1 or LEN(@Item)>0 Begin 
     Insert Into @ListTable (ID, ListValue) Values (@ID, @Item)
     Set @ID = @ID+1
   End
   Set  @CurrentPosition = @NextPosition+@L
   Set  @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
  End
    RETURN
END
  11. ==============================

    11.여기 집계 테이블을 사용하면 제프 MODEN에 의해 ​​하나 개의 분할 문자열 함수 (최상의 방법)입니다

    여기 집계 테이블을 사용하면 제프 MODEN에 의해 ​​하나 개의 분할 문자열 함수 (최상의 방법)입니다

    CREATE FUNCTION [dbo].[DelimitedSplit8K]
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover NVARCHAR(4000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

    탈리 OH에서 언급! 향상된 SQL 8K "CSV 분배기"기능

  12. ==============================

    12.이 블로그는 T-SQL에서 XML을 사용하여 꽤 좋은 솔루션을했다.

    이 블로그는 T-SQL에서 XML을 사용하여 꽤 좋은 솔루션을했다.

    이것은 내가 그 블로그 (필요에 따라 변경 기능 이름 및 결과 유형 캐스트)를 기반으로 내놓았다 기능입니다 :

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitIntoBigints]
(@List varchar(MAX), @Splitter char)
RETURNS TABLE 
AS
RETURN 
(
    WITH SplittedXML AS(
        SELECT CAST('<v>' + REPLACE(@List, @Splitter, '</v><v>') + '</v>' AS XML) AS Splitted
    )
    SELECT x.v.value('.', 'bigint') AS Value
    FROM SplittedXML
    CROSS APPLY Splitted.nodes('//v') x(v)
)
GO
  13. ==============================

    13.

    CREATE Function [dbo].[CsvToInt] ( @Array varchar(4000)) 
returns @IntTable table 
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int 
declare @array_value varchar(4000) 

set @array = @array + ','

while patindex('%,%' , @array) <> 0 
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
  14. ==============================

    14.

    /* *Object:  UserDefinedFunction [dbo].[Split]    Script Date: 10/04/2013 18:18:38* */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(@List varchar(8000),@SplitOn Nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
    Set @List = Replace(@List,'''','')
    While (Charindex(@SplitOn,@List)>0)
    Begin

    Insert Into @RtnValue (value)
    Select
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END
go

Select *
From [Clv].[Split] ('1,2,3,3,3,3,',',')
GO
  15. ==============================

    15.그 문제가 해결 될 것입니다 후에는 SQL 서버에서이 기능을 쓰기.

    그 문제가 해결 될 것입니다 후에는 SQL 서버에서이 기능을 쓰기.

    http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html

    16. from https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 선택 *를 사용하지 않는 이유는 무엇입니까?  (0) 2020.03.07
[SQL] PostgreSQL의 열 이름은 대소 문자를 구분?  (0) 2020.03.07
[SQL] 어떻게 SQL에서 임의의 행을 요청하는?  (0) 2020.03.06
[SQL] MySQL은 하나를 제외한 모든 중복 행을 삭제 하시겠습니까? [복제]  (0) 2020.03.06
[SQL] MySQL은 어떻게 범위에없는 날짜를 채우기 위해?  (0) 2020.03.06
,

티스토리툴바