How to Expand Comma Separated Values into Separate Rows Using SQL Server 2005

How do I expand comma separated values into separate rows using SQL Server 2005?

I arrived this question 10 years after the post.
SQL server 2016 added STRING_SPLIT function.
By using that, this can be written as below.

declare @product table
(
    ProductId int,
    Color     varchar(max)
);
insert into @product values (1, 'red, blue, green');
insert into @product values (2, null);
insert into @product values (3, 'purple, green');

select
    p.ProductId as ProductId,
    ltrim(split_table.value) as Color
from @product p
outer apply string_split(p.Color, ',') as split_table;

Split comma separated string table row into separate rows using TSQL

Alternatively, you could use XML like so:

DECLARE @yourTable TABLE(ID INT,SomeValue VARCHAR(25));

INSERT INTO @yourTable
VALUES  (1,'a,b,c,d'),
        (2,'e,f,g');

WITH CTE
AS
(
    SELECT  ID,
            [xml_val] = CAST('<t>' + REPLACE(SomeValue,',','</t><t>') + '</t>' AS XML)
    FROM @yourTable
)

SELECT  ID,
        [SomeValue] = col.value('.','VARCHAR(100)')
FROM CTE
CROSS APPLY [xml_val].nodes('/t') CA(col)

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

How to convert comma separated value into rows in sql server

You have tagged your question with SQL Server 2016, in SQL Server 2016 there is a new function STRING_SPLIT.

In SQL Server 2016 your query should be as simple as:

declare @tab table ([user_name] varchar(10),Unit varchar(100))
insert into @tab
VALUES ('ABC','1,2')

SELECT t.[user_name]
       , Value as Unit
FROM @tab t 
    CROSS APPLY STRING_SPLIT(t.Unit , ',') 

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 |

Splitting delimited values in a SQL column into multiple rows

If you are on SQL Server 2016+

You can use the new STRING_SPLIT function, which I've blogged about here, and Brent Ozar has blogged about here.

SELECT s.[message-id], f.value
  FROM dbo.SourceData AS s
  CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

If you are still on a version prior to SQL Server 2016

Create a split function. This is just one of many examples out there:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

I've discussed a few others here, here, and a better approach than splitting in the first place here.

Now you can extrapolate simply by:

SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

Also I suggest not putting dashes in column names. It means you always have to put them in [square brackets].

T-SQL: split and aggregate comma-separated values

You will want to use a split function:

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;

And then you can query the data in the following manner:

select items, count(items)
from table1 t1
cross apply dbo.split(t1.id, ',')
group by items

See SQL Fiddle With Demo

Insert Comma seperated varchar into multiple column value?

You could do the following steps:

1) Create a split function for your comma separated string:

CREATE FUNCTION [dbo].[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 = ','

    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 = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
    END

    IF @Item IS NOT NULL
    BEGIN
          SET @Item = @ItemList
          INSERT INTO @Items VALUES (@Item)
    END

    ELSE INSERT INTO @Items VALUES (@InputString)

    RETURN

END

2) Create a procedure that receives as parameter the comma separated string. In this example I'm using your fixed string but you should modify this to make it work with a parameter.
Note: I'm taking in consideration that your destination table has always 3 columns and that the comma separated string has a length of a multiple of 3 always... You must adapt the procedure if this change...

/* Get the length of the comma separated string */
DECLARE @ITEM_COUNT INT

SELECT @ITEM_COUNT = COUNT(*) FROM
(
    SELECT  item
        FROM Split('"AccountType","contains","Customer","Balance","equals",250,"FirstName","like","John"',',')  
) N

declare @x int
set @x = 1

/* Insert in your table every 3 columns... */
WHILE (@x < @ITEM_COUNT)
BEGIN 

    insert into test 
    select /* pivoting the sub-query */
      fieldname = max(case when seq = @x then item end),
      fieldcondition = max(case when seq = @x + 1 then item end),
      fieldvalue = max(case when seq = @x + 2 then item end)
    from 
    (
        SELECT  item
               ,row_number() OVER (ORDER BY (SELECT 1)) AS seq 
                FROM Split('"AccountType","contains","Customer","Balance","equals",250,"FirstName","like","John"',',')  
    ) a

set @x = @x + 3

END

Hope this helps

---

Related Topics