Split String Lists Using Multiple Delimiters

This article helps database developers understand how to split a string using multiple delimiters. The common practice has been to split the string using a single delimiter that you find in many sources. But, when it comes to a delimiter list, this article will help you to split those strings in a much easier way.

Let's first create the following function, before getting into the splitting of strings. This function will generate the sequence of numbers up to the given number. 

create function dbo.fn_generate_numbers

(@numrows int)
returns @returntable table (rownum int primary key)

as

begin

declare @idt int

set @idt = 0

while (@idt < @numrows)

begin

select @idt = @idt + 1

insert into @returntable

select @idt

end

return

end

go

Here we go with the variable creation and the assigning of the values. 

declare

@inputstring varchar(max),

@delimiterlist varchar(32)

declare @xml as xml

declare @derivedstring varchar(max)=''

set @inputstring = 'http://www.google.com;http://www.yahoo.com|http://www.msn.com~http://www.twitter.com,

http://www.facebook.com~http://www.sqlservercentral.com;http://www.social.technet.microsoft.com,

http://www.sqlmag.com;http://www.sqlperformance.com,http://www.sqlteam.com'

set @delimiterlist = ';|,~'

 How this script works. 

• Using the function "fn_generate_numbers" we are creating the sequence of numbers for the given input string length.
• We are identifying the pattern of the delimiter list and generalizing the delimiter value for the given input string.
• We are replacing the generic delimiter value with the XML node and converting the complete string into XML format.
• Finally doing the XML manipulation using xquery we are generating the string list with generatenumbers.

 Step 1 

 

select rownum as [n] from dbo.fn_generate_numbers(len(@inputstring))

 Step 2  

select @derivedstring = @derivedstring + case when patindex('%['+ @delimiterlist + ']%',substring(@inputstring,n,1))>0 then ','
else substring(@inputstring,n,1)
end from generatenumbers;

Step 3  

set @xml = cast(('<root>'+replace(@derivedstring,

',','</root><root>')+'</root>') as xml)

 Step 4 

select distinct replace(a.value('.', 'varchar(max)'),char(13)+char(10),'') as [stringlist] from @xml.nodes('root') as fn(a)

That's it. Now you will get the desired result-set for your given input string. Making this script as a user defined function will be helpful for your development activity.

User defined function

create function dbo.[fn_split_string_using_multiple_delimiters]

(

     @inputstring varchar(max),

     @delimiterlist varchar(32)

)

returns @returntable table(rowid int identity primary key,items varchar(max))

begin

How this script works

Step 1
  
Using the function "fn_generate_numbers" we are creating the sequence of numbers for the given input string length.

Step 2

We are identifying the pattern of the delimiter list and generalizing the delimiter value for the given input string.

Step 3

We are replacing the generic delimiter value with the XML node and converting the complete string into XML format.

Step 4

Finally doing the XML manipulation using xquery we are generating the string list.

*/ declare

@xml as xml

declare @derivedstring varchar(max)='' ;with generatenumbers as

 Step 1 

 

select rownum as [n] from dbo.fn_generate_numbers(len(@inputstring))

 Step 2  

select

@derivedstring = @derivedstring + case when patindex('%['+ @delimiterlist + ']%',substring(@inputstring,n,1))>0 then ','
else
    substring(@inputstring,n,1) end from generatenumbers;

 Step 3  

set @xml = cast(('<root>'+replace(@derivedstring,

',','</root><root>')+'</root>') as xml)

 Step 4  

Insert into @returntable

select distinct replace(a.value('.', 'varchar(max)'),char(13)+char(10),'') as [stringlist] from @xml.nodes('root') as fn(a)

return;

end

go

Here are some examples

select * from dbo.[fn_split_string_using_multiple_delimiters]('india;uk ; usa ; spain;italy',';')

select * from dbo.[fn_split_string_using_multiple_delimiters]('india,uk ; usa ; spain:italy',',;:')

select * from [fn_split_string_using_multiple_delimiters](

'http://www.google.com;http://www.yahoo.com|http://www.msn.com~http://www.twitter.com,

http://www.facebook.com~http://www.sqlservercentral.com;http://www.social.technet.microsoft.com,

http://www.sqlmag.com;http://www.sqlperformance.com,http://www.sqlteam.com',';|,~')

Now whenever we want to split the string using the delimiter list or delimiter, then this function will help you to speed up your development activity.