SQL 2005 Split Comma Separated Column on Delimiter

SQL 2005 Split Comma Separated Column on Delimiter

Yes, it's possible with CROSS APPLY (SQL 2005+):

with testdata (CommaColumn, ValueColumn1, ValueColumn2) as (
  select 'ABC,123', 1, 2 union all
  select 'XYZ, 789', 2, 3
  ) 
select 
  b.items as SplitValue
, a.ValueColumn1
, a.ValueColumn2
from testdata a
cross apply dbo.Split(a.CommaColumn,',') b

Notes:

1. You should add an index to the result set of your split column, so that it returns two columns, IndexNumber and Value.

2. In-line implementations with a numbers table are generally faster than your procedural version here.

eg:

create function [dbo].[Split] (@list nvarchar(max), @delimiter nchar(1) = N',')
returns table
as
return (
  select 
    Number = row_number() over (order by Number)
  , [Value] = ltrim(rtrim(convert(nvarchar(4000),
        substring(@list, Number
        , charindex(@delimiter, @list+@delimiter, Number)-Number
        )
    )))
  from dbo.Numbers
  where Number <= convert(int, len(@list))
    and substring(@delimiter + @list, Number, 1) = @delimiter
  )

Erland Sommarskog has the definitive page on this, I think: http://www.sommarskog.se/arrays-in-sql-2005.html

SQL Server 2005: How to create a new column from comma-delim data across 2 columns

Check This.

        SELECT  staffID, 
        substring(( select ','+ concat(s.Data,a.Data)  AS 'data()' 
         from
        (
            SELECT A.staffID,  
            Split.a.value('.', 'VARCHAR(100)') AS Data  ,
            row_number() over (order by (select 1) ) as rank
         FROM  
         (
             SELECT staffID,  
                 CAST ('<M>' + REPLACE(modificationstaff, ',', '</M><M>') + '</M>' AS XML) AS Data  

             FROM  #table
         ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
         )s  inner join

          (SELECT A.staffID,  
             Split.a.value('.', 'VARCHAR(100)') AS Data  ,
             row_number() over (order by (select 1) ) as rank

         FROM  
         (
             SELECT staffID,  
            CAST ('<M>' + REPLACE(modificationdate, ',', '</M><M>') + '</M>' AS XML) AS Data  
             FROM  #table
         ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
         )a   on s.rank=a.rank                       
        FOR XML PATH('') 
        ), 2, 1000)  as NewColumn
        FROM  #table

OutPut :

If You dont have function Split() then first create it.

create FUNCTION [dbo].[Split]
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

Splitting of comma separated values

You can create a user defined UDF like the one shown below. Then, just pass in the comma separated list from another query and it will return a table with each value in a separate row.

CREATE FUNCTION [dbo].[fnSplitStringAsTable] 
(
    @inputString varchar(MAX),
    @delimiter char(1) = ','
)
RETURNS 
@Result TABLE 
(
    Value varchar(MAX)
)
AS
BEGIN
    DECLARE @chIndex int
    DECLARE @item varchar(100)

    -- While there are more delimiters...
    WHILE CHARINDEX(@delimiter, @inputString, 0) <> 0
        BEGIN
            -- Get the index of the first delimiter.
            SET @chIndex = CHARINDEX(@delimiter, @inputString, 0)

            -- Get all of the characters prior to the delimiter and insert the string into the table.
            SELECT @item = SUBSTRING(@inputString, 1, @chIndex - 1)

            IF LEN(@item) > 0
                BEGIN
                    INSERT INTO @Result(Value)
                    VALUES (@item)
                END

            -- Get the remainder of the string.
            SELECT @inputString = SUBSTRING(@inputString, @chIndex + 1, LEN(@inputString))
        END

    -- If there are still characters remaining in the string, insert them into the table.
    IF LEN(@inputString) > 0
        BEGIN
            INSERT INTO @Result(Value)
            VALUES (@inputString)
        END

    RETURN 
END

SQL split based on delimeter, keep only second element

SELECT RIGHT(ColName , LEN(ColName) - CHARINDEX('\', ColName) )
FROM TABLEName

OR

SELECT PARSENAME(REPLACE(ColName , '\' , '.'),1)
FROM TableName

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 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;

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 |

Comma delimited record in a table (Split/Merge) on SQL server 2005

I worked on it too, I don't think it performs awesomely, but here is my solution :

SELECT
    t.ID
    ,t.Value
    ,STUFF((
        SELECT ',' + t2.Description
        FROM 
            table1 as t1
            inner join table2 as t2
                on (',' + LTRIM(RTRIM(t1.Value)) + ',') LIKE '%,' + CONVERT(VARCHAR(10),t2.Value) + ',%'
        WHERE t1.ID = t.ID
        ORDER BY t2.Description --Can be removed
        FOR XML PATH('')
    ), 1, 1, '') as description
FROM 
    table1 as t

The subquery will join values from table 1 with the value of table 2 and retrieve descriptions. Then use FOR XML PATH and STUFF to concatenate the multiple rows description into a single row.

You can see the SQLFiddle Demo here

Splitting comma delimited cell data

The below function will split you comma sep column into a table. You will then need to iterate through the temp table and insert 1 row into your parcelOwners table using the data from your single column. To get this to work you will need an outer loop to iterate through the parcelOwners table and an inner loop to iterate through the @temptable for each row. Also, don't forget, if you come to a row in your outer loop with no comma's in the owner_id column you won't want to do anything.

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
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 

---

Related Topics