Split Function equivalent in tsql?

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.

From stackoverflow

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

  Sung Meister : wow, why would anyone mark down this answer without explanation?

  XOR : Thanks, I would also like to know. Is there an error here? I wrote this code perhaps 6 years ago and it was working OK since when.

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

  A selection of different methods

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

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

• 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?