Split a separator row into a table with multiple rows and columns

Question

Please, could you help, I am new to SQL and have come across the script below. I used google and tried to find a solution, but failed.

I have a temporary table named TEMP with one column with names and rows, depending on how long the csv row is. When you select * FROM #TEMP (temporary table), it returns data as shown below:

results

88.47,1,263759,10.00 | 303.53,2,264051,13.00 | 147.92,3,264052,6.00 | 43.26,4,268394,10.00 | 127.7,5,269229,4.00 |

Please use the link below to see what results look right from the database:
http://design.northdurban.com/DatabaseResult.png

I need a solution that reads this data from an existing temporary table and inserts it into another temporary table with rows and columns, for example, in the link below, for example:

The required output is displayed in the link below.

http://design.northdurban.com/capture.png

Please help as I am sure this post will help many other users since I did not find any existing solution.

Answer 1

First convert a string to strings using the | separator |

 DECLARE @str VARCHAR(max)='88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|' SELECT Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))) FROM (SELECT Cast ('<M>' + Replace(@str, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) 

then convert the result to another column using parsename trick

 SELECT Id,c1,c2,c3 FROM (SELECT Id=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 4), ';', '.'), C1=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 3), ';', '.'), c2=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 2), ';', '.'), c3=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 1), ';', '.') FROM (SELECT Cast ('<M>' + Replace(@str, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)) a WHERE id IS NOT NULL 

SQLFIDDLE DEMO

Update: To improve performance, try this.

 SELECT c1,c2,c3,c4 FROM (SELECT C1=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 4), ';', '.'), C2=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 3), ';', '.'), C3=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 2), ';', '.'), C4=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 1), ';', '.') FROM (SELECT Split.a.value('.', 'VARCHAR(100)') col FROM (SELECT Cast ('<M>' + Replace(@str, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))v) a WHERE c1 IS NOT NULL; 

Update2: To parse more than one row from a table, use this code.

Example data table

 create table #test(string varchar(8000)) insert into #test values ('88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|'), ('88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|') 

Request

 SELECT c1,c2,c3,c4 FROM (SELECT C1=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 4), ';', '.'), C2=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 3), ';', '.'), C3=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 2), ';', '.'), C4=Replace(Parsename(Replace(Replace(col, '.', ';'), ',', '.'), 1), ';', '.') FROM (SELECT Split.a.value('.', 'VARCHAR(100)') col FROM (SELECT Cast ('<M>' + Replace(string, '|', '</M><M>') + '</M>' AS XML) AS Data from #test) AS A CROSS APPLY Data.nodes ('/M') AS Split(a))v) a WHERE c1 IS NOT NULL; 

Answer 2

This will only work if you have 4 columns. In this situation, you can do the following

 SELECT REPLACE(PARSENAME(REPLACE(REPLACE(ColumnName, '.', '~'), ',', '.'), 4), '~', '.'), REPLACE(PARSENAME(REPLACE(REPLACE(ColumnName, '.', '~'), ',', '.'), 3), '~', '.'), REPLACE(PARSENAME(REPLACE(REPLACE(ColumnName, '.', '~'), ',', '.'), 2), '~', '.'), REPLACE(PARSENAME(REPLACE(REPLACE(ColumnName, '.', '~'), ',', '.'), 1), '~', '.') From #TEMP 

Answer 3

You can write a table value function to parse a string as follows:

 CREATE FUNCTION dbo.parseData ( @stringToSplit VARCHAR(MAX) ) RETURNS @return TABLE (ID int, Column1 real, Column2 int, Column3 int, Column4 real) AS BEGIN DECLARE @char char; DECLARE @len int = LEN(@stringToSplit); DECLARE @buffer varchar(50) = ''; DECLARE @field int = 1; DECLARE @Column1 real DECLARE @Column2 int DECLARE @Column3 int DECLARE @Column4 real DECLARE @row int = 1 DECLARE @i int = 1; WHILE @i <= @len BEGIN SELECT @char = SUBSTRING(@stringToSplit, @i, 1) IF @char = ',' BEGIN IF @field = 1 SET @Column1 = CONVERT(real, @buffer); ELSE IF @field = 2 SET @Column2 = CONVERT(int, @buffer); ELSE IF @field = 3 SET @Column3 = CONVERT(int, @buffer); SET @buffer = ''; SET @field = @field + 1 END ELSE IF @char = '|' BEGIN SET @Column4 = CONVERT(real, @buffer); INSERT INTO @return (ID, Column1, Column2, Column3, Column4) VALUES (@row, @Column1, @Column2, @Column3, @Column4); SET @buffer = ''; SET @row = @row + 1 SET @field = 1 END ELSE BEGIN SET @buffer = @buffer + @char END SET @i = @i + 1; END RETURN END GO 

And then call this function as follows:

 SELECT Col1 = '88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|' INTO #Temp1; INSERT INTO #Temp1 VALUES ('88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|') SELECT data.* INTO #Temp2 FROM #Temp1 CROSS APPLY parseData(#Temp1.Col1) as data SELECT * FROM #Temp2 DROP TABLE #Temp1 DROP TABLE #Temp2 

Performance:

So, I conducted a performance test of this method against the method described by NoDisplayName. Over 10,000 iterations of my technique took 13,826, and NoDisplayName took 36,176, so mine takes only 38% of the time when NoDisplayName does.

To test this, I used the Azure database and ran the following script.

 -- First two queries to check the results are the same. -- Note the Parsename technique returns strings rather than reals which is why -- the last column has .00 at the end of the numbers in the Parsename tecnique. DECLARE @str VARCHAR(max)='88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.01|' SELECT c1,c2,c3, c4 FROM (SELECT C1=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 4), ';', '.'), C2=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 3), ';', '.'), C3=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 2), ';', '.'), C4=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 1), ';', '.') FROM (SELECT Cast ('<M>' + Replace(@str, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)) a WHERE c1 IS NOT NULL; SELECT * FROM dbo.parseData(@str) GO -- Now lets time the Parsename method over 10,000 itterations SET NOCOUNT ON; DECLARE @str VARCHAR(max)='88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|' DECLARE @i int = 0 declare @table table (c1 decimal, c2 int, c3 int, c4 decimal) DECLARE @Start datetime = GETDATE(); while @i < 1000 begin INSERT INTO @table SELECT c1,c2,c3, c4 FROM (SELECT C1=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 4), ';', '.'), C2=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 3), ';', '.'), C3=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 2), ';', '.'), C4=Replace(Parsename(Replace(Replace(Rtrim(Ltrim(Split.a.value('.', 'VARCHAR(100)'))), '.', ';'), ',', '.'), 1), ';', '.') FROM (SELECT Cast ('<M>' + Replace(@str, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)) a WHERE c1 IS NOT NULL; DELETE FROM @table; set @i = @i + 1; end DECLARE @End datetime = GETDATE() PRINT CONVERT(nvarchar(50),@Start,126) + ' - ' + convert(nvarchar(50),@End,126) + ' - ' + convert(nvarchar(50), DATEDIFF(ms, @start, @end)) GO -- Now the my technique over 10,000 itterations SET NOCOUNT ON; DECLARE @str VARCHAR(max)='88.47,1,263759,10.00| 303.53,2,264051,13.00| 147.92,3,264052,6.00| 43.26,4,268394,10.00| 127.7,5,269229,4.00|' DECLARE @i int = 0 declare @table table (c1 decimal, c2 int, c3 int, c4 decimal) DECLARE @Start datetime = GETDATE(); while @i < 1000 begin INSERT INTO @table SELECT * FROM dbo.parseData(@str) DELETE FROM @table; set @i = @i + 1; end DECLARE @End datetime = GETDATE() PRINT CONVERT(nvarchar(50),@Start,126) + ' - ' + convert(nvarchar(50),@End,126) + ' - ' + convert(nvarchar(50), DATEDIFF(ms, @start, @end)) GO