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