Split data that is between Commas

Auerelio Vasquez
Auerelio Vasquez used Ask the Experts™
on
Hi I would like to do this:

I have a column that has data stored in two columns Id, topic

It could look like this

ID.    Topic
1.      ABC, def, this

What I want returned is

1 abc
1 def
1 this

I've tried parsebame but it doesn't do exactly this

Thanks for help !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Software Team Lead
Commented:
first defined a Table-valued function such as:

Create function [dbo].[SplitString] 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS Item
from tokens
);

Open in new window

and then call it like:

declare @yourTable table
(
	ID int,
	Topic Varchar(100)
)
insert into @yourTable values
(1, 'ABC, def, this'),
(2, '123, 456, 789, hello, world');

select a.ID, ltrim(rtrim(b.Item)) Topic
from @yourTable a
outer apply dbo.SplitString(a.Topic,',') b

Open in new window

Nitin SontakkeDeveloper

Commented:
And just FYI, if you have SQL Server 2016, there is built-in function string_split.
SharathData Engineer
Commented:
try this.
create table table1 (id int, topic varchar(100));
insert into table1 values (1, 'abc,def,this'),(2, 'abc,this');

SELECT id, ltrim(td.value('.', 'NVARCHAR(255)')) AS topic
  FROM table1
CROSS APPLY (SELECT  CAST(('<td><![CDATA[' + REPLACE(topic, ',',']]></td><td><![CDATA[') + ']]></td>') AS XML) AS XmlData) ToXml
CROSS APPLY XmlData.nodes('td') A ( td );

/*
  	id	topic
1	1	abc
2	1	def
3	1	this
4	2	abc
5	2	this

*/

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial