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.