Comma-separated String into Table's Column in SQL Server

Question

I am working with SQL Server, I have successfully converted a table's rows into comma-separated values, now I want to convert that string of comma-separated values back to table's rows.

I have this string (Varchar)

DECLARE @str AS varchar(Max)
SET @str = '0.00,0.00,1576.95,0.00,4105.88,1017.87,0.00,6700.70'

I want these values into rows.

Like

0.00
0.00
1576
...

possible duplicate of [How to split/explode comma delimited string field into SQL query](http://stackoverflow.com/questions/19334380/how-to-split-explode-comma-delimited-string-field-into-sql-query) — Vignesh Kumar A, Mar 04 '14 at 05:49
duplicate of http://stackoverflow.com/questions/2647/split-string-in-sql but the chosen answer for that one still uses an inefficient WHILE. — Solomon Rutzky, Mar 04 '14 at 05:53

score 2 · Answer 1 · answered Mar 04 '14 at 06:11

This question is a duplicate of a few others, but some of the accepted answers are still the inefficient WHILE loops or recursive CTEs. There are three ways to accomplish a split that won't kill performance:

Numbers / Tally Table: http://www.sqlservercentral.com/articles/Tally+Table/72993/ (free registration required)
XML: https://www.simple-talk.com/blogs/2012/01/05/using-xml-to-pass-lists-as-parameters-in-sql-server/
SQLCLR: there are numerous examples of this on many sites but your best (and easiest) bet is to either grab the one provided in the Numbers / Tally Table article noted above OR just install SQL# (SQLsharp) (I am the author of SQL# but String_Split and String_Split4k are in the Free version).

Raging Bull · Accepted Answer · 2014-03-04T05:37:53.180

Create a function:

CREATE FUNCTION [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    DECLARE @Index INT
    DECLARE @Slice nvarchar(4000)
    -- HAVE TO SET TO 1 SO IT DOESN’T EQUAL ZERO FIRST TIME IN LOOP
    SELECT @Index = 1
    WHILE @Index !=0
        BEGIN
            SELECT @Index = CHARINDEX(@Delimiter,@String) --Getting the indexof the first Occurrence of the delimiter

            -- Saving everything to the left of the delimiter to the variable SLICE
            IF @Index !=0
                SELECT @Slice = LEFT(@String,@Index - 1)
            ELSE
                SELECT @Slice = @String

            -- Inserting the value of Slice into the Results SET
            INSERT INTO @Results(Items) VALUES(@Slice)

            --Remove the Slice value from Main String
            SELECT @String = RIGHT(@String,LEN(@String) - @Index)

            -- Break if Main String is empty
            IF LEN(@String) = 0 BREAK
        END
    RETURN
END

Pass the string @str and the delimiter (,) to the function.

SELECT Items FROM [dbo].[Split] (@str, ',')

It will return the result as a table:

Items

0.00
0.00
1576.95
0.00
4105.88
1017.87
0.00
6700.70

See SQL Fiddle

Please do NOT use a WHILE loop split function. It is very inefficient. There are a couple of much better options. This question is a duplicate anyway and I will post the link in the moment. — Solomon Rutzky, Mar 04 '14 at 05:45

score 0 · Answer 3 · answered Mar 04 '14 at 05:43

Try this Function

CREATE FUNCTION UF_CSVToTable_new(@psCSString VARCHAR(8000))
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)
  WHILE LEN(@psCSString) > 0
  BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
  END
RETURN
END

Usage

DECLARE @str AS varchar(Max)
SET @str = '0.00,0.00,1576.95,0.00,4105.88,1017.87,0.00,6700.70'
SELECT * FROM UF_CSVToTable_new(@str)

Result

enter image description here

Please do NOT use a WHILE loop split function. It is very inefficient. This function will be ok only for very small lists but does not scale well at all. A lot of testing has been done around splitting in T-SQL by many people and the three methods known to work properly are noted in my answer. — Solomon Rutzky, Mar 04 '14 at 14:55

Comma-separated String into Table's Column in SQL Server

3 Answers3

Linked