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
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
