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
  3. (@numrows int)
  4. returns @returntable table (rownum int primary key)
  6. as
  8. begin
  10. declare @idt int
  12. set @idt = 0
  14. while (@idt < @numrows)
  16. begin
  18. select @idt = @idt + 1
  20. insert into @returntable
  22. select @idt
  24. end
  26. return
  28. end
  30. go

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

  1. declare
  3. @inputstring varchar(max),
  5. @delimiterlist varchar(32)
  7. declare @xml as xml
  9. declare @derivedstring varchar(max)=''
  11. set @inputstring = 'http://www.google.com;http://www.yahoo.com|http://www.msn.com~http://www.twitter.com,
  13. http://www.facebook.com~http://www.sqlservercentral.com;http://www.social.technet.microsoft.com,
  15. http://www.sqlmag.com;http://www.sqlperformance.com,http://www.sqlteam.com'
  17. set @delimiterlist = ';|,~'

How this script works.

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,
  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]
  3. (
  5. @inputstring varchar(max),
  7. @delimiterlist varchar(32)
  9. )
  11. returns @returntable table(rowid int identity primary key,items varchar(max))
  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
  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
  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,
  3. ',','</root><root>')+'</root>') as xml)

Step 4

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