In my previous article I discussed about “How to split string based on single delimiter”. In this article, we will discuss how to split the string based on multiple delimiters and also remove the spaces around the string.
Given below is the user defined function to achieve this result.
--Create this user deinfed function.
CREATE FUNCTION dbo.[UDF_Split_Based_On_Multiple_Delimiters]
(
@String VARCHAR(MAX), -- Variable for string
@delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE( --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
DECLARE @Xml AS XML
DECLARE @REVISED_STRING VARCHAR(MAX)
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @REVISED_STRING=STUFF((SELECT '' + (Case When
PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
FROM N4 Nums WHERE Nums.n<=LEN(@String) FOR XML PATH('')),1,0,'')
SET @Xml = cast(('<a>'+replace(@REVISED_STRING,
',','</a><a>')+'</a>') AS XML)
INSERT INTO @Table SELECT A.value('.', 'varchar(max)')
as [Column] FROM @Xml.nodes('a') AS FN(a)
RETURN
END
GO
--Syntax SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('abc,def ; ghij ; kl',',;:')
GO
--OUTPUT
Splitcolumn
———–
abc
def
ghij
kl
(4 row(s) affected)
This may not be the best solution. Let me know if you have better solution than this to split it.
SQL Server Portal 
doesnt work
Kindly post the sample, that is not working.
Thanks
Hi Muhammad. I tried this as well on SQL Server 2012 Enterprise. Ran it exactly as above and got an empty result. Might it be server setup? E.g. collation? Ours is SQL_Latin1_General_CP1_CI_AS.
Issue seems to be after the SELECT @RevisedString statement, since this statement produces a comma-delimited string as expected. I’ve short-cutted the issue here by avoiding the xml at the end and using some other code we have to populate the @table.
Hi Richard,
Can you please post the modified script. Thanks
Imran
Hi Richard,
Fixed the issue. Basically, it was the capital letter (‘A’) creating problem.
Thanks,
Imran
Hi Imran,
I really liked your code its good but I found one bug and have fixed the issue.
Issue Details:
Let’s consider my below scenario and pipe(‘|’) as delimiter then in the string if we have space between the words it will be shrinked hence we will be losing our original string.
To be more clear let’s consider
String=’I like sql server|very much’ if we pass this in your function with pipe(|) as delimiter output will be Ilikesqlserver and verymuch so space will be removed and which will be incorrect.
Hence I have fixed the above issue now I can run above code with the proper output.
select * from dbo.UDF_Split_Based_On_Multiple_Delimiters_v1(‘I like sql server|very much ‘,’|’)
output will be I like sql server and I like sql server.
Here is modified Code:
create FUNCTION dbo.UDF_Split_Based_On_Multiple_Delimiters_v1
(
@String VARCHAR(MAX), — Variable for string
@delimiter VARCHAR(50) — Delimiter in the string
)
RETURNS @Table TABLE( –Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
DECLARE @Xml AS XML
DECLARE @REVISED_STRING VARCHAR(MAX)
SET @String=REPLACE(@String,’ ‘,’!!!!’)
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @REVISED_STRING=STUFF((SELECT ” + (Case When
PATINDEX(‘%[‘ + @delimiter + ‘]%’,SUBSTRING(@String,Nums.n,1)) >0
Then ‘,’ else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
FROM N4 Nums WHERE Nums.n<=LEN(@String) FOR XML PATH('')),1,0,'')
SET @Xml = cast(('‘+replace(@REVISED_STRING,
‘,’,’‘)+’‘) AS XML)
INSERT INTO @Table SELECT REPLACE (A.value(‘.’, ‘varchar(max)’),’!!!!’,’ ‘)
as [Column] FROM @Xml.nodes(‘a’) AS FN(a)
RETURN
END
Please let me know your comments on the same.
Thanks,
Ravi Airodagi
I really liked your code its good but I found one bug and have fixed the issue.
Issue Details:
Let’s consider my below scenario and pipe(‘|’) as delimiter then in the string if we have space between the words it will be shrinked hence we will be losing our original string.
To be more clear let’s consider
String=’I like sql server|very much’ if we pass this in your function with pipe(|) as delimiter output will be Ilikesqlserver and verymuch so space will be removed and which will be incorrect.
Hence I have fixed the above issue now I can run above code with the proper output.
Here is modified Code:
create FUNCTION dbo.UDF_Split_Based_On_Multiple_Delimiters_v1
(
@String VARCHAR(MAX), — Variable for string
@delimiter VARCHAR(50) — Delimiter in the string
)
RETURNS @Table TABLE( –Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
DECLARE @Xml AS XML
DECLARE @REVISED_STRING VARCHAR(MAX)
SET @String=REPLACE(@String,’ ‘,’!!!!’)
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)
SELECT @REVISED_STRING=STUFF((SELECT ” + (Case When
PATINDEX(‘%[‘ + @delimiter + ‘]%’,SUBSTRING(@String,Nums.n,1)) >0
Then ‘,’ else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
FROM N4 Nums WHERE Nums.n<=LEN(@String) FOR XML PATH('')),1,0,'')
SET @Xml = cast(('‘+replace(@REVISED_STRING,
‘,’,’‘)+’‘) AS XML)
INSERT INTO @Table SELECT REPLACE (A.value(‘.’, ‘varchar(max)’),’!!!!’,’ ‘)
as [Column] FROM @Xml.nodes(‘a’) AS FN(a)
RETURN
END
Please let me know your comments on the same.