在T-SQL中，Split函数是等价的吗?

I’m looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a table or table variable.

我想分裂的1、2、3、4、5,6,7,8,9,10,11,12日,13日,14日,15日…(逗号分隔)为一个表或表变量。

Does anyone have a function that returns each one in a row?

每个人都有一个函数返回每一行吗?

15 个解决方案

#1

Here is somewhat old-fashioned solution:

这里有一些老式的解决方案:

/*
    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

In SQL Server 2008 you can achieve the same with .NET code. Maybe it would work faster, but definitely this approach is easier to manage.

在SQL Server 2008中，你可以用。net代码实现同样的效果。也许它会运行得更快，但显然这种方法更容易管理。

#2

Try this

试试这个

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)

OR

或

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

Many more ways of doing the same is here How to split comma delimited string?

这里还有很多方法可以用逗号分隔字符串?

#3

You've tagged this SQL Server 2008 but future visitors to this question (using SQL Server 2016+) will likely want to know about STRING_SPLIT.

您已经标记了这个SQL Server 2008，但是未来的访问者(使用SQL Server 2016+)可能会想知道STRING_SPLIT。

With this new builtin function you can now just use

有了这个新的builtin函数，您现在就可以使用了。

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

Some restrictions of this function and some promising results of performance testing are in this blog post by Aaron Bertrand.

这一功能的一些限制和性能测试的一些有希望的结果在这篇博文中由Aaron Bertrand撰写。

#4

This is most like .NET, for those of you who are familiar with that function:

这很像。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

here is the split function that u asked

这是u要求的分裂函数。

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

execute the function like this

像这样执行函数。

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

#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)

Source of this response: http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited

此响应的源:http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited。

#7

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

A selection of different methods

选择不同的方法。

#8

I am tempted to squeeze in my favourite solution. The resulting table will consist of 2 columns: PosIdx for position of the found integer; and Value in integer.

我很想挤进我最喜欢的解决方案。结果表将由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

It works by using recursive CTE as the list of positions, from 1 to 100 by default. If you need to work with string longer than 100, simply call this function using 'option (maxrecursion 4000)' like the following:

它使用递归CTE作为位置列表，默认情况下从1到100。如果您需要使用大于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)

#9

This is another version which really does not have any restrictions (e.g.: special chars when using xml approach, number of records in CTE approach) and it runs much faster based on a test on 10M+ records with source string average length of 4000. Hope this could help.

这是另一个没有任何限制的版本(例如:使用xml方法时的特殊字符，CTE方法中记录的数量)，而且它的运行速度要快得多，基于1000万+记录的测试，源字符串平均长度为4000。希望这可以帮助。

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

#10

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

usage

使用

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

#11

/* *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

#12

This blog came with a pretty good solution using XML in T-SQL.

这个博客在T-SQL中使用XML有了一个很好的解决方案。

This is the function I came up with based on that blog (change function name and result type cast per need):

这是我在这个博客上提出的功能(更改函数名称和结果类型转换需要):

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

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

Using tally table here is one split string function(best possible approach) by Jeff Moden

这里使用的是Jeff 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
;

Referred from Tally OH! An Improved SQL 8K “CSV Splitter” Function

被称为从理货哦!改进的SQL 8K“CSV拆分器”功能。

#15

You write this function in sql server after that problem will be solved.

在这个问题解决之后，在sql server中编写这个函数。

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

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