Post
Ask Question

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. 

  1. create function dbo.fn_generate_numbers  
  2.   
  3. (@numrows int)  
  4. returns @returntable table (rownum int primary key)  
  5.   
  6. as  
  7.   
  8. begin  
  9.   
  10. declare @idt int  
  11.   
  12. set @idt = 0  
  13.   
  14. while (@idt < @numrows)  
  15.   
  16. begin  
  17.   
  18. select @idt = @idt + 1  
  19.   
  20. insert into @returntable  
  21.   
  22. select @idt  
  23.   
  24. end  
  25.   
  26. return  
  27.   
  28. end  
  29.   
  30. go  

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

  1. declare  
  2.   
  3. @inputstring varchar(max),  
  4.   
  5. @delimiterlist varchar(32)  
  6.   
  7. declare @xml as xml  
  8.   
  9. declare @derivedstring varchar(max)=''  
  10.   
  11. set @inputstring = 'http://www.google.com;http://www.yahoo.com|http://www.msn.com~http://www.twitter.com,  
  12.   
  13. http://www.facebook.com~http://www.sqlservercentral.com;http://www.social.technet.microsoft.com,  
  14.   
  15. http://www.sqlmag.com;http://www.sqlperformance.com,http://www.sqlteam.com'  
  16.   
  17. 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 

 

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

 Step 2  

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

Step 3  

  1. set @xml = cast(('<root>'+replace(@derivedstring,  
  2.   
  3. ',','</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

  1. create function dbo.[fn_split_string_using_multiple_delimiters]  
  2.   
  3. (  
  4.   
  5.      @inputstring varchar(max),  
  6.   
  7.      @delimiterlist varchar(32)  
  8.   
  9. )  
  10.   
  11. returns @returntable table(rowid int identity primary key,items varchar(max))  
  12.   
  13. 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

  1. @xml as xml  
  2.   
  3. declare @derivedstring varchar(max)='' ;with generatenumbers as  

 Step 1 

 

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

 Step 2  

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

 Step 3  

  1. set @xml = cast(('<root>'+replace(@derivedstring,  
  2.   
  3. ',','</root><root>')+'</root>') as xml)  

 Step 4  

  1. Insert into @returntable  
  2.   
  3. select distinct replace(a.value('.', 'varchar(max)'),char(13)+char(10),'') as [stringlist] from @xml.nodes('root') as fn(a)  
  4.   
  5. return;  
  6.   
  7. end  
  8.   
  9. 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.


Similar Articles