separate comma separated values and store in table in sql server

Question

I am having a stored procedure which gets the comma separated value as an input. I need to separate it and needs to store it in a table as individual rows.

Let the input for SP is :

Rule_ID  ListType_ID  Values
1        2            319,400,521,8465,2013

I need to store it in a table called DistributionRule_x_ListType in the below format:

Rule_ID  ListType_ID  Value
1        2            319
1        2            400
1        2            521
1        2            8465
1        2            2013

My SP looks like below:

ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(@Rule_ID int,
@ListType_ID int,
@Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID,ListType_ID,Value)
    VALUES (@Rule_ID,@ListType_ID,@Values)

END

possible duplicate of [How do I expand comma separated values into separate rows using SQL Server 2005?](http://stackoverflow.com/questions/702968/how-do-i-expand-comma-separated-values-into-separate-rows-using-sql-server-2005) — Prahalad Gaggar, Jul 23 '13 at 07:22

score 25 · Accepted Answer · answered Feb 11 '13 at 12:25

25

You will need to create a split function similar to this:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

Then in your stored procedure, you will call the function to split your string:

ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(
  @Rule_ID int,
  @ListType_ID int,
  @Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID, ListType_ID, Value)
    SELECT @Rule_ID, @ListType_ID, items
    FROM [dbo].[Split] (@Values, ',')  -- call the split function 

END

When you execute the stored procedure, it will split the values and insert the multiple rows into your table:

exec spInsertDistributionRuleListType 1, 2, '319,400,521,8465,2013';

See SQL Fiddle with Demo. This will insert the following result:

| RULE_ID | LISTTYPE_ID | VALUE |
---------------------------------
|       1 |           1 |    10 |
|       1 |           2 |   319 |
|       1 |           2 |   400 |
|       1 |           2 |   521 |
|       1 |           2 |  8465 |
|       1 |           2 |  2013 |

answered Feb 11 '13 at 12:25

Taryn

242,637
56
362
405

how to initialize 'items'.It is showing error as Invalid Column name 'items' – prabu R Feb 11 '13 at 12:50
2

@prabuR did you include the from clause? The from clause calls the split function – Taryn Feb 11 '13 at 12:52
In my SP, I may pass null value to that split function...How to get it work if a null value is passed? – prabu R Feb 11 '13 at 13:21
@prabuR you can use an `IF` statement to use two separate `INSERT` statement, one that uses the split and the other does not -- see this demo -- http://www.sqlfiddle.com/#!3/4179d/1 – Taryn Feb 11 '13 at 13:30
I have tried it already. It works. But I thought of any other easy and effective way of modifying inside the function. Anyway, thanks – prabu R Feb 11 '13 at 13:32
@bluefeet I am working on the same problem. The thing is i am keeping a column as auto-increment which will hold the 'id' and the split values will be stored in the other column. I didn't declare/use 'id' field in procedure but only the values/string i will be splitting. I am getting error like 'Conversion failed when converting the varchar value '110,123,567' to data type int.'. I am confused. Please help me out in this. – Nazaf Anwar Jun 26 '15 at 07:33
Ok, i got it done. The values i was splitting and storing in the table. I was giving column value's Data type as 'int' which was not ok with the input values i was giving. I changed it to varchar and it's working perfectly. Thanks a lot. @bluefeet – Nazaf Anwar Jun 26 '15 at 09:11

Prashant16 · Answer 2 · 2013-02-11T12:21:48.337

you can do it with charindex like

DECLARE @id VARCHAR(MAX)

SET @id = @Values          --'319,400,521,8465,2013,'

WHILE CHARINDEX(',', @id) > 0 
BEGIN

    DECLARE @tmpstr VARCHAR(50)
     SET @tmpstr = SUBSTRING(@id, 1, ( CHARINDEX(',', @id) - 1 ))

    INSERT  INTO DistributionRule_x_ListType
            ( Rule_ID ,
              ListType_ID ,
              Value
            )
    VALUES  ( @Rule_ID ,
              @ListType_ID ,
              @tmpstr)
            )
    SET @id = SUBSTRING(@id, CHARINDEX(',', @id) + 1, LEN(@id))
END

score 3 · Answer 3 · answered Jan 07 '15 at 12:17

You can do this without dbo.Split function.

Here is your sample data

SELECT * INTO #TEMP
FROM
(
     SELECT 1  Rule_ID, 2 ListType_ID, '319,400,521,8465,2013' [Values]
     UNION ALL
     SELECT 1  , 3 , '100,200' 
)TAB

Now execute the following query and will select all comma separated values for each Rule_ID and ListType_ID.

SELECT [Rule_ID],ListType_ID,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT [Rule_ID],ListType_ID,
     CAST ('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM #TEMP     
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

SQL FIDDLE

score 2 · Answer 4 · edited Jul 21 '18 at 08:35

2

select Rule_ID ,ListType_ID,Values as value
FROM table  
    CROSS APPLY STRING_SPLIT(Values, ',');

edited Jul 21 '18 at 08:35

Mohammad Kanan

4,452
10
23
47

answered Jul 21 '18 at 06:21

Sai Kumar Reddy

75
1
2
5

1

While this code snippet may be the solution, [including an explanation](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Narendra Jadhav Jul 21 '18 at 07:12

score 0 · Answer 5 · answered Feb 11 '13 at 12:11

You could create a udf that returns a table var to split the string. We have used the following successfully on MSSQL2005.

CREATE FUNCTION [dbo].[fn_explode] (
    @str_separator NVARCHAR(255),
    @str_string VARCHAR(4000)
)
RETURNS @ret_string_parts TABLE (str_value varchar(4000))
AS
BEGIN
    DECLARE @intPos INT
    DECLARE @intLengthString INT
    DECLARE @intTempPatIndex INT
    DECLARE @intLengthSeparator INT

    SET @str_string = @str_string + @str_separator

    SET @intPos = 0
    SET @intLengthString = LEN(@str_string)
    SET @intLengthSeparator = LEN(@str_separator)



    IF PATINDEX ( '%' + @str_separator + '%' , @str_string ) <= 0 BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    IF @str_separator =  @str_string BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    WHILE @intPos <= @intLengthString
    BEGIN
        SET @intTempPatIndex = PATINDEX('%' + @str_separator + '%', SUBSTRING(@str_string, @intPos,@intLengthString))
        IF @intTempPatIndex = 0 BEGIN
            INSERT INTO @ret_string_parts
            SELECT SUBSTRING(@str_string, @intPos, @intLengthString)
            BREAK
        END
        ELSE BEGIN
            IF @intPos = 0 BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex)
                SET @intPos = @intPos + @intTempPatIndex + @intLengthSeparator
            END
            ELSE BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex-1)
                SET @intPos = @intPos + @intTempPatIndex + (@intLengthSeparator-1)
            END

        END
    END

    RETURN
END

score 0 · Answer 6 · answered Jun 23 '16 at 19:03

Completing @bluefeet answer, you could also use the CSV string to store multiple values in multiple columns:

 --input sql text 
declare @text_IN varchar(max) ='text1, text1.2, text1.3, 1, 2010-01-01\r\n text2, text2.2, text2.3, 2, 2016-01-01'

Split the csv file into rows:

declare @temptable table (csvRow varchar(max))    
declare @DelimiterInit varchar(4) = '\r\n'
declare @Delimiter varchar(1) = '|'
declare @idx int       
declare @slice varchar(max)    

set @text_IN = REPLACE(@text_IN,@DelimiterInit,@Delimiter)


select @idx = 1       
    if len(@text_IN)<1 or @text_IN is null  return       

while @idx!= 0       
begin       
    set @idx = charindex(@Delimiter,@text_IN)       
    if @idx!=0       
        set @slice = left(@text_IN,@idx - 1)       
    else       
        set @slice = @text_IN 

    if(len(@slice)>0)  
        insert into @temptable(csvRow) values(@slice)       

    set @text_IN = right(@text_IN,len(@text_IN) - @idx)       
    if len(@text_IN) = 0 break       
end

Split rows into columns:

;WITH XMLTable (xmlTag)
AS
(
    SELECT CONVERT(XML,'<CSV><champ>' + REPLACE(csvRow,',', '</champ><champ>') + '</champ></CSV>') AS xmlTag
    FROM @temptable
)

 SELECT RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[1]','varchar(max)'))) AS Column1,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[2]','varchar(max)'))) AS Column2,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[3]','varchar(max)'))) AS Column3,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[4]','int'))) AS Column4,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[5]','datetime'))) AS Column5
 FROM XMLTable

score 0 · Answer 7 · answered Jun 01 '22 at 11:05

CREATE FUNCTION UDF_STRING_SPLIT
(
    @tags VARCHAR(4000),
    @Delimiter varchar(1)
)
RETURNS @Result TABLE 
(   
    Id VARCHAR(100)
)
AS
BEGIN
      DECLARE @Curr_COUNT INT = LEN(@tags), 
              @Curr_removable_length INT = 0

      WHILE (@Curr_COUNT > 0)
         BEGIN
 
           IF(Charindex(@Delimiter,@tags) > 0 )
             BEGIN
                 INSERT INTO @Result VALUES((SELECT RTRIM(LTRIM(LEFT(@tags ,Charindex(@Delimiter,@tags)-1)))))
                 SET @Curr_removable_length = Charindex(@Delimiter,@tags) + 1;
                 SET @tags = substring(@tags, @Curr_removable_length,len(@tags))
                 SET @Curr_COUNT = lEN(@tags)
             END
         ELSE
             BEGIN
                 INSERT INTO @Result VALUES((SELECT RTRIM(LTRIM(@tags))))
                 BREAK;
             END
  
         END

     RETURN

END

score 0 · Answer 8 · answered Jun 01 '22 at 13:06

select Rule_ID ,ListType_ID,Values as value
FROM table  
CROSS APPLY STRING_SPLIT(Values, ',');

It is a table-valued function that splits a string into rows of substrings, based on a specified separator character

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Reference from here

separate comma separated values and store in table in sql server

8 Answers8

Linked