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?

score 13 · Accepted Answer · answered Nov 10 '13 at 04:11

13

assuming your split returns column name item

insert <table> (colname)
select y.item
from dbo.SplitString(@testString, ':') x
cross apply
dbo.SplitString(x.item, ',') y

answered Nov 10 '13 at 04:11

t-clausen.dk

43,517
12
59
92

score 5 · Answer 2 · answered Jun 08 '16 at 11:15

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

M.Ali · Answer 3 · 2013-11-10T01:45:40.643

Function Definition by Romil
Create this Function in your Sql Server

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item                 VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

Pass Your Parameteres
Since it is a Table Function you will SELECT * FROM this_Function like you would do with a table

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 Split(@testString, ',')

Result Set

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

Your Existing Code

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

The Second Parameter needs to be the deliminator, Since you are Passing : as the second parameter it is breaking you string where ever it finds : in you passed string which is obviously in 2 places and you get back 3 values/Strings in the result set

                          String1/Value1                             String2/Value2                    String3/Value3
set @testString = '72594206916,2,1/2/08,Tacoma,WA  :   72594221856,5,5/7/13,San Francisco,CA  :  72594221871,99,12/30/12,Dallas,TX'

Great code: Just a small comment - you need to write 'declare @testString varchar(102)' and not varchar(100) because otherwise TX gets missed out! — gordon613, Jun 28 '16 at 15:45

score 2 · Answer 4 · answered Apr 08 '14 at 17:46

Something similar to what M.Ali gave above, but shorter:

Use [Database_Name]
Go;

 CREATE FUNCTION Split
(
@RowData nvarchar(MAX),
@SplitOn nvarchar(MAX)
)  
RETURNS @RtnValue table 
(
Data nvarchar(MAX)
) 
AS  
BEGIN 
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Then select from the function as in the above example:

USE [Database_Name]
GO
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 Split(@testString, ',')

score 1 · Answer 5 · answered Oct 11 '17 at 18:29

declare @fqdn_list varchar(max) = 'test1.qa.local,test2.qa.local,test3.qa.local'
-- temp table
DECLARE @fqdn_tbl TABLE (   fqdn nvarchar(50) )

INSERT INTO @fqdn_tbl  SELECT LTRIM(RTRIM(split.a.value('.', 'NVARCHAR(MAX)'))) AS fqdn   FROM (
    SELECT CAST ('<M>' + REPLACE(@fqdn_list, ',', '</M><M>') + '</M>' AS XML) AS data   ) AS a   CROSS APPLY data.nodes ('/M') AS split(a)

select * from  @fqdn_tbl

score 1 · Answer 6 · answered Feb 28 '20 at 17:46

1

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/

answered Feb 28 '20 at 17:46

Paulie

19
1

Maybe improve the answer with a small code example. Also: what if compatibility is < 130? – Claudio Valerio Feb 28 '20 at 20:27
This is a tip, not a complete answer, therefore suited to a comment. – Dale K Jul 29 '21 at 02:22

score 0 · Answer 7 · edited Jul 29 '21 at 02:22

0

Try this code:

SELECT value
FROM STRING_SPLIT(@testString, ',')

edited Jul 29 '21 at 02:22

Dale K

25,246
15
42
71

answered Oct 03 '20 at 12:26

Okan Pulukcu

1
4

score -1 · Answer 8 · answered Sep 06 '16 at 12:29

-1

You forgot the last line. In your example, the 'TX' is not taken into account. You shuld check the remaining text avec While loop

answered Sep 06 '16 at 12:29

mmedz

1

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

8 Answers8

Linked