Split string using multiple delimiters in SQL SERVER

Many times we are Using split function in SQL SERVER for split a string using some delimiters

Example:

If we have a string like  '10:0,11:1,12:3,13:4,15:5,16:6'

And we need to split the string using comma delimiter then usually we will use some split functions

Example :

CREATE FUNCTION dbo.SplitFunction(@String varchar(8000), @Delimiter char(1))        

returns @temptable TABLE (items varchar(8000))        

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   

   

Then we can call this function like

select items from dbo.SplitFunction('10:0,11:1,12:3,13:4,15:5,16:6',',')

Output :

Items

------

10

0,11

1,12

3,13

4,15

5,16

6

This is normally we all have done

But here we want to split a string using multiple delimiters

Example in our above string we want to split using “comma” and “semicolon”

We can see how we can do that.

Method 1:

This is one of the simplest methods. Use the below function it will do the task

CREATE FUNCTION dbo.MultipleDelemiterSplit

(

   @List       NVARCHAR(MAX),

   @Delemiter1 Varchar(100),

   @Delemiter2 Varchar(100)

)

RETURNS  TABLE

AS

   RETURN

   ( 

      SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')

      FROM

      (

        SELECT x = CONVERT(XML, '<cnt>'

          + REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''), '</cnt><cnt>') , ISNULL(@Delemiter2,''), '</cnt><cnt>')

          + '</cnt>').query('.')

      ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)

   );

GO 

Select * From dbo.MultipleDelemiterSplit ('10:0,11:1,12:3,13:4,15:5,16:6',',',':')

OutPut:

Item

------

10

0

11

1

12

3

13

4

15

5

16

6

Method 2:

We can use REPLACE function

select REPLACE('10:0,11:1,12:3,13:4,15:5,16:6',':',',')

It will replace all occurance of semicolon with a comma,after replacing we can use normal split function for splitting

Example :

select items from dbo. SplitFunction (REPLACE('10:0,11:1,12:3,13:4,15:5,16:6',':',','),',') This will also give the same result.

Method 3:

We can use CROSS APPLY for doing the same

SELECT e.items from dbo. SplitFunction ('10:0,11:1,12:3,13:4,15:5,16:6',',') d

CROSS APPLY dbo. SplitFunction (d.items,':') e

This will also give the expected output.

Method 4  : 

Generalized approach for N number of delimiters

alter FUNCTION dbo.MultipleDelemiterSplit
(
   @List       NVARCHAR(MAX)
 
)
RETURNS  TABLE
AS

   RETURN
   (

      SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, '<cnt>'
          + @List
          + '</cnt>').query('.')
      ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)
   );

GO

-----------------------------

DECLARE @yourDelimiterString VARCHAR(1000)

DECLARE @teststring VARCHAR(1000)

SET @yourDelimiterString = '_(){}.-+-'

SET @teststring = 'test(arun)need.to{remove}all+unwanted-'

;WITH CTE AS

(

  SELECT SUBSTRING(@yourDelimiterString, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]

  UNION ALL

  SELECT SUBSTRING(@yourDelimiterString, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 

  FROM CTE 

  WHERE [Counter] < LEN(@yourDelimiterString)

)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '</cnt><cnt>') FROM CTE

if right(@teststring,11) = '</cnt><cnt>'  

set @teststring=(select substring(@teststring, 1, (len(@teststring) - 11)))

if left(@teststring,11) = '</cnt><cnt>' 

set @teststring=Stuff(@teststring, 1, 11, '')

select * from dbo.MultipleDelemiterSplit(@teststring)

Output 

---------

item

----

test

arun

need

to

remove

all

unwanted