Separate Comma Separated Values and Store in Table in SQL Server

separate comma separated values and store in table in sql server

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 |

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

Save a comma separated array in a string in a table

You can use string_split() in the most recent versions of SQL Server:

SELECT ID, Name, ss.value as subject
FROM Students s CROSS APPLY
     string_split(s.subjects, ',') ss;

You can also play with JSON or define your own split() function, although in older versions, I would just use a recursive CTE:

with cte as (
      select s.id, convert(varchar(max), null) as subject, convert(varchar(max), subjects + ',') as rest
      from students s
      union all
      select id, left(rest, charindex(',', rest) - 1),
             stuff(rest, 1, charindex(',', rest), '')
      from cte
      where rest <> ''
     )
select *
from cte
where subject is not null;

Here is a db<>fiddle.

Split comma delimited string and insert to a table (int)

Using the Split() function you have mentioned in comments,

-- Variable holding comma separated values
DECLARE @Var VARCHAR(4000);
SET @Var =  '188,189,190,191,192,193,194'

-- Test Target Table
DECLARE @Target_Table TABLE  (First_ID INT,Second_ID INT,Third_ID INT) 

-- Insert statement
INSERT INTO @Target_Table
SELECT 1, CAST(Items AS INT) , 0 
FROM  dbo.Split(@Var, ',')  

-- Test Select
SELECT * FROM  @Target_Table  

Result Set

╔══════════╦═══════════╦══════════╗
║ First_ID ║ Second_ID ║ Third_ID ║
╠══════════╬═══════════╬══════════╣
║        1 ║       188 ║        0 ║
║        1 ║       189 ║        0 ║
║        1 ║       190 ║        0 ║
║        1 ║       191 ║        0 ║
║        1 ║       192 ║        0 ║
║        1 ║       193 ║        0 ║
║        1 ║       194 ║        0 ║
╚══════════╩═══════════╩══════════╝  

Split Comma Separated values into multiple column

Your sample data may not need any splitting. You want to move the data to a column based on the value it finds. You can do this a bit simpler than splitting the data. This works just fine for your sample data.

declare @Something table
(
    Combined_Column varchar(10)
)

insert @Something values
('1,2,3')
, ('2')
, ('1,3')
, ('1,2,3,4')
, ('1,3,4')
, ('1')
, ('4')

select *
    , col1 = case when charindex('1', s.Combined_Column) > 0 then 1 end
    , col2 = case when charindex('2', s.Combined_Column) > 0 then 2 end
    , col3 = case when charindex('3', s.Combined_Column) > 0 then 3 end
    , col4 = case when charindex('4', s.Combined_Column) > 0 then 4 end
from @Something s

Split comma separated values into target table with fixed number of columns

It is typically bad design to store CSV values in a single column. If at all possible, use an array or a properly normalized design instead.

While stuck with your current situation ...

For known small maximum number of elements

A simple solution without trickery or recursion will do:

SELECT id, 1 AS rnk
     , split_part(csv, ', ', 1) AS c1
     , split_part(csv, ', ', 2) AS c2
     , split_part(csv, ', ', 3) AS c3
     , split_part(csv, ', ', 4) AS c4
     , split_part(csv, ', ', 5) AS c5
FROM   tbl
WHERE  split_part(csv, ', ', 1) <> '' -- skip empty rows

UNION ALL
SELECT id, 2
     , split_part(csv, ', ', 6)
     , split_part(csv, ', ', 7)
     , split_part(csv, ', ', 8)
     , split_part(csv, ', ', 9)
     , split_part(csv, ', ', 10)
FROM   tbl
WHERE  split_part(csv, ', ', 6) <> '' -- skip empty rows

-- three more blocks to cover a maximum "around 20"

ORDER  BY id, rnk;

db<>fiddle here

id being the PK of the original table.

This assumes ', ' as separator, obviously.

You can adapt easily.

Related:

• Split comma separated column data into additional columns

For unknown number of elements

Various ways. One way use regexp_replace() to replace every fifth separator before unnesting ...

-- for any number of elements
SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 1) AS c1
     , split_part(c.csv5, ', ', 2) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 4) AS c4
     , split_part(c.csv5, ', ', 5) AS c5
FROM   tbl t
     , unnest(string_to_array(regexp_replace(csv, '((?:.*?,){4}.*?),', '\1;', 'g'), '; ')) WITH ORDINALITY c(csv5, rnk)
ORDER  BY t.id, c.rnk;

db<>fiddle here

This assumes that the chosen separator ; never appears in your strings. (Just like , can never appear.)

The regular expression pattern is the key: '((?:.*?,){4}.*?),'

(?:) ... “non-capturing” set of parentheses

() ... “capturing” set of parentheses

*? ... non-greedy quantifier

{4}? ... sequence of exactly 4 matches

The replacement '\1;' contains the back-reference \1.

'g' as fourth function parameter is required for repeated replacement.

Further reading:

• PostgreSQL & regexp_split_to_array + unnest
• Apply `trim()` and `regexp_replace()` on text array
• PostgreSQL unnest() with element number

Other ways to solve this include a recursive CTE or a set-returning function ...

Fill from right to left

(Like you added in How to put values starting from the right side into columns?)

Simply count down numbers like:

SELECT t.id, c.rnk
     , split_part(c.csv5, ', ', 5) AS c1
     , split_part(c.csv5, ', ', 4) AS c2
     , split_part(c.csv5, ', ', 3) AS c3
     , split_part(c.csv5, ', ', 2) AS c4
     , split_part(c.csv5, ', ', 1) AS c5
FROM ...

db<>fiddle here

Comma Separated Values in one table to return results from another table

As a starter: don't store multiple values in a single column; don't use strings to store numbers. You can have a look at this famous SO post for more details on why this is discouraged.

That said, a simple (although inefficient) solution to search for a value in a CSV list is:

select t1.FirstName + ' ' + t1.Lastname as fullname, t2.Postcode, t2.HouseNo
from t1
join t2 on concat(', ', t2.id, ', ') like concat('%, ', t1.id, ', %')
where t1.active = 1

This assumes that you consistently use a comma + a space (', ') as separator between list elements.

---

Related Topics