Split out a field of comma separated values based on a unique code in the same row.

Question

Split out a field of comma separated values based on a unique code in the same row.

Trawler

Old Hand

Points: 393
More actions
October 21, 2014 at 3:45 am

#312318

Hi
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Take a look at this excellent splitter function by Jeff Moden[/url].

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

greatrajani SSC Journeyman Points: 97 More actions · Answer 2

hi!

kindly go through below link

http://msdn.microsoft.com/en-IN/library/ms178129.aspx

there is provision of bulk insert and bcp which is ultra fast in execution

Regards

Rajani Karthik

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

greatrajani (11/24/2014)
hi!
kindly go through below link
http://msdn.microsoft.com/en-IN/library/ms178129.aspx
there is provision of bulk insert and bcp which is ultra fast in execution
Regards
Rajani Karthik

While I applaud the innovative thought, that would mean that you would have to first export the table to a file, import the file into a table, and then unpivot the table to get the desired output of 1 row per element. That would hardly be an "ultra fast" execution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

@ Trawler,

Are you all set on this one?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Koen Verbeeck (10/21/2014)
Take a look at this excellent splitter function by Jeff Moden[/url].

Thank you for the kudo, Koen. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Trawler Old Hand Points: 393 More actions · Answer 6

Koen Verbeeck - Tuesday, October 21, 2014 4:02 AM
Take a look at this excellent splitter function by Jeff Moden[/url].

The answer is this if you have SQL Server 2016 STRING_SPLIT ( string , separator )
Prior to SQL Server 2016 the answer for me was FnSplitFieldOnComma as below...

USE [XXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnSplitFieldOnComma](@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;

Trawler Old Hand Points: 393 More actions · Answer 7

Jeff Moden - Monday, November 24, 2014 10:09 PM
@ Trawler,Are you all set on this one?

The answer is this if you have SQL Server 2016 STRING_SPLIT ( string , separator )
Prior to SQL Server 2016 the answer for me was a function named FnSplitFieldOnComma .

[/ALTER FUNCTION [dbo].[fnSplitFieldOnComma](@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;]

sgmunson SSC Guru Points: 110639 More actions · Answer 8

Trawler - Thursday, April 20, 2017 2:19 AM
Jeff Moden - Monday, November 24, 2014 10:09 PM
@ Trawler,Are you all set on this one?
The answer is this if you have SQL Server 2016 STRING_SPLIT ( string , separator )
Prior to SQL Server 2016 the answer for me was a function named FnSplitFieldOnComma .

[/ALTER FUNCTION [dbo].[fnSplitFieldOnComma](@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;]

All the way back in SQL 2008 (and ever since), Jeff's string splitter function was blowing the performance doors off of code like that (for prior to the SQL 2016 STRING_SPLIT function). If you still have that older code in place, it would be far superior to replace it with Jeff's DelimitedSplit8K function.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

Trawler - Thursday, April 20, 2017 2:13 AM
Koen Verbeeck - Tuesday, October 21, 2014 4:02 AM
Take a look at this excellent splitter function by Jeff Moden[/url].
The answer is this if you have SQL Server 2016 STRING_SPLIT ( string , separator )
Prior to SQL Server 2016 the answer for me was FnSplitFieldOnComma as below...
USE [XXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitFieldOnComma](@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;

That method is what is known as a "Split Nibbler". Here's a performance chart on various types of splitters. The "Split Nibbler" method is the Blue line. The thin Black line with the label of "????" is the splitter that Koen and Steve are talking about and that's before an additional optimization.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Trawler Old Hand Points: 393 More actions · Answer 10

Trawler - Thursday, April 20, 2017 2:19 AM
Jeff Moden - Monday, November 24, 2014 10:09 PM
@ Trawler,Are you all set on this one?
The answer is this if you have SQL Server 2016 STRING_SPLIT ( string , separator )
Prior to SQL Server 2016 the answer for me was a function named FnSplitFieldOnComma .

[/ALTER FUNCTION [dbo].[fnSplitFieldOnComma](@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;]

Thanks for the replies.