How to split string and insert values into table in SQL Server

Question

I have a string like this:

72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX

This is basically 5 values in each of 3 rows (from an ASP.NET grid). I need to split this string apart into 5 columns and 3 rows in a SQL Server table. Individual values are separated by commas and rows by colons.

I found a function to split a string into pieces and I can get the rows out of this string:

declare @testString varchar(100)
set @testString = '72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX'

select *
from dbo.SplitString(@testString, ':')

gives me:

72594206916,2,1/2/08,Tacoma,WA
72594221856,5,5/7/13,San Francisco,CA
72594221871,99,12/30/12,Dallas,TX

This gives me a result set with the three rows (the function outputs a table). Can I call this function again at the same time and insert its output into a table somehow?

Answer 1

Create this SQL function:

CREATE FUNCTION [dbo].[StringSplit](@input NVARCHAR(MAX), @delimiter CHAR(1)=',') 
       RETURNS @returnTable TABLE(item NVARCHAR(100)) AS  
     BEGIN 
        IF @input IS NULL RETURN;
        DECLARE @currentStartIndex INT, @currentEndIndex INT,@length INT;
        SET @length=LEN(@input);
        SET @currentStartIndex=1;

        SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);
        WHILE (@currentEndIndex<>0)
          BEGIN
            INSERT INTO @returnTable VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @currentEndIndex-@currentStartIndex)))
            SET @currentStartIndex=@currentEndIndex+1;
            SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);
          END

        IF (@currentStartIndex <= @length)
          INSERT INTO @returnTable 
            VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @length-@currentStartIndex+1)));
        RETURN;
     END;

Usage example:

DECLARE @testString VARCHAR(100)
SET @testString = '72594206916,2,1/2/08,Tacoma,WA:72594221856,5,5/7/13,San Francisco,CA:72594221871,99,12/30/12,Dallas,TX'

SELECT *
FROM [dbo].[StringSplit](@testString, DEFAULT)

Result (table):

72594206916
2
1/2/08
Tacoma
WA:72594221856
5
5/7/13
San Francisco
CA:72594221871
99
12/30/12
Dallas

Answer 2

If your database is at Compatibility 130 or later, you can use a built-in function STRING_SPLIT:

https://database.guide/how-to-convert-a-comma-separated-list-into-rows-in-sql-server/