Split Function in Sql Server using Set base approach

Niladri.biswas
Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 7555
Well, it is a very well known requirement to have a function that will do the string splitting. In this one we will look into some of them using SET BASE way

Option 1 (Using XQuery Approach)



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringSplitter]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[StringSplitter]
GO
CREATE FUNCTION [dbo].[StringSplitter](@str varchar(8000), @delimiter char(1))

RETURNS TABLE AS
RETURN
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,Items = Split.a.value('.', 'VARCHAR(100)')
FROM
(
SELECT
CAST('<X>' + REPLACE(@str, @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata
) X
CROSS APPLY Splitdata.nodes('/X') Split(a)


Option 2 (Using another XQuery Approach)

CREATE FUNCTION [dbo].[StringSplitter](@str VARCHAR(8000), @delimiter CHAR(1))
RETURNS @SplitTable TABLE (
itemNumber INT
,individualItems VARCHAR(8000)
)
AS
BEGIN

DECLARE @xml XML
= CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML)

INSERT INTO @SplitTable (itemNumber,individualItems)

SELECT
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,Data.value('.', 'VARCHAR(100)')
FROM @xml.nodes('X') X(Data)

RETURN
END


Option 3 (Using Number Table Approach by using master.dbo.spt_values)

CREATE FUNCTION [dbo].[StringSplitter](@str VARCHAR(8000), @delimiter CHAR(1))
RETURNS @SplitTable TABLE (
itemNumber INT
,individualItems VARCHAR(8000)
)
AS
BEGIN

SET @str = @str + @delimiter

INSERT INTO @SplitTable (itemNumber,individualItems)

SELECT
ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,RIGHT(
LEFT(
@str,Number-1
)
,PATINDEX(
'%' + @delimiter + '%'
,REVERSE(
LEFT(
@delimiter+@str
,Number-1
)
)
)
)
FROM master.dbo.spt_values
WHERE TYPE = 'P'
AND Number BETWEEN 1 AND LEN(@str)
AND SUBSTRING(@str,Number,1) = @delimiter

RETURN
END


Option 4 (Using Recursive CTE Approach)

CREATE FUNCTION [dbo].[StringSplitter](@str varchar(8000), @delimiter char(1))
RETURNS TABLE AS
RETURN
WITH Cte AS(
SELECT
StartIndex = 0
,EndIndex = CHARINDEX(@delimiter,@str)
UNION ALL
SELECT
EndIndex+1
,CHARINDEX(@delimiter,@str,EndIndex+1)
FROM Cte
WHERE EndIndex>0 )

SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,Items = SUBSTRING(
@str
,StartIndex
,COALESCE(NULLIF(EndIndex,0),LEN(@str)+1)-StartIndex
)
FROM Cte


Option 5 (Using another Recursive CTE Approach)

CREATE FUNCTION [dbo].[StringSplitter](@str varchar(8000), @delimiter char(1))
RETURNS TABLE AS
RETURN
WITH Cte AS(
SELECT
StartIndex = 0
,EndIndex = 1
UNION ALL
SELECT
EndIndex
,CHARINDEX(@delimiter, @str, EndIndex) + LEN(@delimiter)
FROM Cte
WHERE EndIndex > StartIndex )

SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,Items = SUBSTRING(
@str
,StartIndex
, CASE
WHEN EndIndex > LEN(@delimiter)
THEN EndIndex-StartIndex-LEN(@delimiter)
ELSE LEN(@str) - StartIndex + 1
END
)
FROM Cte
WHERE StartIndex > 0


We can use the above functions as under

Case 1: On a single variable
[Code]
Declare @str Varchar(100) = 'string1,string2,string3,string4'
Declare @delimiter Varchar(2) = ','

Select
[Original Data] = @str
,*
From dbo.StringSplitter(@str,@delimiter)


Case 2: On a table column
[Code]
Declare @str Varchar(100) = 'string1,string2,string3,string4'
Declare @delimiter Varchar(2) = ','
Declare @t TABLE(Data VARCHAR(MAX))
INSERT INTO @t values(@str)
Select
[Original Data] = t.Data
,sp.*
From @t t
Cross Apply
dbo.StringSplitter(t.Data,@delimiter) AS sp


Result in both the cases

Original Data ItemNumber Items
------------ ----------- ------
string1,string2,string3,string4 1 string1
string1,string2,string3,string4 2 string2
string1,string2,string3,string4 3 string3
string1,string2,string3,string4 4 string4


Even sometime we may need to use them directly into the queries. In that case we can adopt the below approach

StringSplitter in conjunction with a Table column (using Option 1 code)

Declare @str Varchar(100) = 'string1,string2,string3,string4'
Declare @delimiter Varchar(2) = ','
Declare @t TABLE(Data VARCHAR(MAX))
INSERT INTO @t values(@str)

Select
Splitdata= Split.a.value('.', 'VARCHAR(100)')
From
(
Select
Cast('<X>' + Replace(Data, @delimiter , '</X><X>') + '</X>' AS XML) AS Splitdata
From @t
) X
Cross Apply Splitdata.nodes('/X') Split(a)


OR

StringSplitter in conjunction with a single variable (using Option 5 code)

Declare @str Varchar(100) = 'string1,string2,string3,string4'
Declare @delimiter Varchar(2) = ','

;WITH Cte AS(
SELECT
StartIndex = 0
,EndIndex = 1
UNION ALL
SELECT
EndIndex
,CHARINDEX(@delimiter, @str, EndIndex) + LEN(@delimiter)
FROM Cte
WHERE EndIndex > StartIndex )

SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY(SELECT 1))
,Items = SUBSTRING(
@str
,StartIndex
, CASE
WHEN EndIndex > LEN(@delimiter)
THEN EndIndex-StartIndex-LEN(@delimiter)
ELSE LEN(@str) - StartIndex + 1
END
)
FROM Cte
WHERE StartIndex > 0


Hope this helps. I would like to hear more approaches using SET BASE for the same.

Comments or Responses

Posted by: T.saravanan on: 10/30/2011 Level:Silver | Status: [Member] [MVP] | Points: 10
Nice & More info...

Continue your great effort in our DNF.

Login to post response