Split Function equivalent in tsql?

Question

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. Does anyone have a Function that returns each one in a row?

I am using SQL Server 2008.

Thanks.

Answer 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.

Answer 2

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

A selection of different methods

Answer 3

Erland Sommarskog has maintained the authoritative answer to this question for the last 12 years: http://www.sommarskog.se/arrays-in-sql.html

It's not worth reproducing all of the options here on StackOverflow, just visit his page and you will learn all you ever wanted to know.

Answer 4

Hi, 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.

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:

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)

Answer 5

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'
;with cte as
(
select 0 a, 1 b
union all
select b, charindex(',', @str, b) + len(',')
from cte
where b > a
)
select substring(@str,a,
case when b > len(',') then b-a-len(',') 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?