Hello,

You can also use CTEs under SQL Server 2005 to do the same thing.

The advantage is that you can specify a JOIN on a CTE :

DECLARE @str VARCHAR(64) 
SET @str = 'toto,titi,tutu,tata'; 
 
WITH CTE (Deb, Fin) AS 
( 
  SELECT 1 Deb, CHARINDEX(',', @str + ',') Fin 
 UNION ALL 
  SELECT Fin + 1, CHARINDEX(',', @str + ',', Fin + 1) 
  FROM CTE 
  WHERE CHARINDEX( ',', @str + ',', Fin + 1 ) > 0 
) 
SELECT SUBSTRING(@str, Deb , Fin - Deb), Deb, Fin 
FROM CTE 
 
-------------------------------------------
DECLARE @String VARCHAR(64) SET @String = 'un,deux,trois,quatre,cinq'; 
DECLARE @Separator CHAR(1) SET @Separator = ','; 
WITH INDICES AS 
( 
    SELECT 0 Deb, 1 Fin 
  UNION ALL 
    SELECT Fin, CHARINDEX(@Separator, @String, Fin) + LEN(@Separator) 
    FROM INDICES 
    WHERE Fin > Deb 
) 
SELECT SUBSTRING( 
          @String, 
          Deb, 
          CASE 
            WHEN Fin > LEN(@Separator) THEN Fin - Deb - LEN(@Separator) 
            ELSE LEN(@String) - Deb + 1 
          END 
        ) String, 
        Deb, 
        Fin, 
        LEN(@String) - Deb + 1 Lgr 
FROM INDICES WHERE Deb > 0


Thanks for the alternate approaches.

Thanks so much for your script.  You saved me ages of time.

As in most examples I have seen, this works great as long as you have a variable or supply a delimited string for it to work on.  I'm sure you know what RBAR means, and I'm trying to avoid that by diong set related processing, sin instead of typing in a string, what if I have a table with onle colum in it and 2000 entries of string text I want to process?  All the examples I have tried return one recors, I want to iterate (try not to use the word loop) through all 2000 entries and insert the results into a table, but I can't get to work, and there are no examples of this, but hundreds of examples on how to do just one string.

Robert.

@Robert.. You have to create table valued function as mentioned in the post and then cross apply or outer apply (depends on your requirement) with your table with the column having delimited string.

Select (fn.Resultcolumn_function) From yourtable AS y CROSS APPLY tablevaluedfunction (y.delimitedstring, delimiter) AS fn

@Venkat - thanks for your reply.  I have updated the tip based on your feedback.

There are faster T-SQL solution which use set-based processing. Some are detailed here: http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html

From Jeff Moden's efforts, this is the best performing UDF I have seen which uses a Numbers table

@ListVARCHAR(8000)
,@DelimiterCHAR(1)
) RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--this UNIONs a "1" (first element) with n+1 values (subsequent elements) relative to delimiter positions
--eliminates need to prepend a delimiter
WITH ElementStartCTE
AS
(
SELECT 1 AS Start
UNION ALL
SELECT
n+1
FROM
dbo.Numbers WITH (NOLOCK)
WHERE
n <= DATALENGTH(@List)
ANDSUBSTRING(@List,n,1) = @Delimiter
)

,ElementStartAndLengthCTE
AS
(
SELECT
Start
,ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,Start),0)-Start,DATALENGTH(@List)) AS Length
FROM
ElementStartCTE
)

SUBSTRING(@List,Start,Length) AS Item
FROM
ElementStartAndLengthCTE;

In a cooperative effort over the last few years Jeff Moden over at SQLServerCentral has pretty much put this issue to rest. 

http://www.sqlservercentral.com/articles/Tally+Table/72993/ ... go to the bottom of the article and download all the code.

The function DelimitedSplit8K has been objectively performance tested against many other non-CLR versions. Jeff Moden even provides the testing script and procedures for anyone to use. The latest version of the function does not require an external numbers/tally table. I have personally tested this and competing versions of splitter functions using Jeff's test harness and DelimitedSplit8K cannot be beat without using a CLR.

By the way, Jeff also provides the code for a splitter CLR (and test results) and the CLR is by far the fastest splitter method. But if one is working on servers without full sysadmin control it is often not possible to build and run CLRs. Both functions produce exactly the same output columns so they can be used interchageably.

The DelimitedSplit8K function's only limitation is that the string being parsed must be be less than VARCHAR(8000) or NVARCHAR(4000). Splitting a value that requires a VARCHAR(MAX) datatype will kill performance and there are some other splitters that handle those rare situations better, though splitting such blobs will always be a performance problem. The function is also optimized for a single delimiter only with values of any size including random sizes. For strings with multiple delimiters (i.e., 2-dimenisonal) there is a variation of DelimitedSplit8K that has been shown to perform faster than trying to do multiple CROSS APPLYs with the same query. Details on that variation can be found here: http://www.sqlservercentral.com/Forums/FindPost1401102.aspx

DelimitedSplit8K is an iTVF and thus produces a virtual table as output. As in the example in a previous post the function can be used by using a JOIN or a CROSS APPLY.

Here is the code for anyone to use:

/***************************************************************************************/

CREATE FUNCTION [dbo].[DelimitedSplit8K]

--===== Define I/O parameters

       (

        @pString VARCHAR(8000)

       ,@pDelimiter CHAR(1)

       )

--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!

RETURNS TABLE

       WITH SCHEMABINDING

       AS

 RETURN

       --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...

     -- enough to cover VARCHAR(8000)

  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 ;

/***************************************************************************************/

Also, for those needing an example of how to CROSS APPLY a tvf splitter function:

/***********************************************************************/

DECLARE 

    @InputString VARCHAR(8000)

   ,@Delimiter1 CHAR(1)

--this would be an example of a delimited string to be split

SET @InputString = '38469,38471,38474'

SET @Delimiter1 = ','

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [Col1] INT NULL,

    [Col2] NVARCHAR(50) NULL,

    PRIMARY KEY (ID))

--this is a sample data with keys to be matched against the values in the delimited string

INSERT INTO #TempTable

SELECT 38469,'Bob'

UNION

SELECT 38471,'George'

UNION

SELECT 38473,'John'

UNION

SELECT 38474,'Alice'

UNION

SELECT 38474,'Carol'

UNION

SELECT 38477,'Randy'

--this query uses a CROSS APPLY to match the table keys with the values in the delimited string

SELECT

    ID

   ,Col1

   ,Col2

FROM

    #TempTable AS tt

CROSS APPLY

    dbo.DelimitedSplit8K(@InputString,@Delimiter1) AS dsk

WHERE 

    tt.Col1 = dsk.Item

Thanks!!!

The above examples gets the values into different rows. How to get the all the values of one id into one row and different columns.

Like - 

Column1  Column2              Column3 Column4 Column5

1              SQL Server 2000  SQL Server 2005   SQL Server 2008  SQL Server 2012

@Shariz - take a look at these tips on PIVOT

http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/