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.