Wednesday 18 November 2015

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







6 comments:

  1. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, function to splie string using different delimiters in SQL Server.

    http://www.dbrnd.com/2015/12/sql-serverfunction-to-split-string-value-using-different-delimiters/

    ReplyDelete
  2. can you please send the modified code which accepts more than 2 delimeters..like 5 to 6 delimiters

    ReplyDelete
    Replies
    1. Hi sorry for the delay. You can refer Asma shaik comment for doing the same. But if you have lots of delimiters and you don't want to append that delimiters with your strings then you can simply replace all your delimiter with a single delimiter and use can use the above function
      Sample :

      input : abcd_def.ghi+klo-ll:
      output: abcd
      def
      ghi
      klo
      ll

      you can use the below method for that
      declare @abc nvarchar(4000) ;
      set @abc=(select

      replace(replace(replace(replace('abcd_def.ghi+klo-ll', '_', '_'), '.', '_'), '+', '_'), '-', '_'))


      Select * From dbo.SplitFunction (@abc,'_')

      Delete
  3. I have used below code to use 7 delimiters.....
    here the data (i.e; string which needs to be split ) we are sending hardcoded...I want the table data to pass this to a function...how can i do this

    SELECT x = CONVERT(XML, ''
    + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(@List, ISNULL(@Delemiter1,''),
    '') ,
    ISNULL(@Delemiter2,''),
    ''),ISNULL(@Delemiter3,''),''),
    ISNULL(@Delemiter4,''),''),
    ISNULL(@Delemiter5,''),''),
    ISNULL(@Delemiter6,''),''),
    ISNULL(@Delemiter7,''),'')
    + '').query('.')



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

    ReplyDelete
  4. Also I want to display the delimeter also to be displayed.....could you please suggest

    ReplyDelete
  5. Hi Asma if you want to display the delimiter then you can use the below method
    alter 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, ''
    + REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''),''+ @Delemiter1) , ISNULL(@Delemiter2,''), ''+@Delemiter2)
    + '').query('.')
    ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)
    );

    GO

    ReplyDelete