SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd [12345] ee [abcde] ee ','[',']') --returns 12345 and abcde
SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd #12345# ee #abcde# ee ','#','#') --returns 12345 and abcde
SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd (12345) ee abcde( ee ','(',')') --returns 12345
ALTER FUNCTION dbo.EmbeddedSplit8K
(
@pString VARCHAR(8000)
,@pDelimiterStart CHAR(1)
,@pDelimiterEnd CHAR(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS
-- Author: Carlo.Romagnano
-- Date : 20181123
-- Starting from DelimitedSplit8K of Jeff Moden
-- [EmbeddedSplit8K] returns all token delimited from @pDelimiterStart and @pDelimiterEnd
-- @pDelimiterStart and @pDelimiterEnd may be the same character
-- e.g SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd [12345] ee [abcde] ee ','[',']') --returns 12345 and abcde
-- e.g SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd #12345# ee #abcde# ee ','#','#') --returns 12345 and abcde
-- N.B. if the delimiters doesn't match the token is not returned.
-- e.g SELECT * FROM [dbo].[EmbeddedSplit8K]('ddd ddd (12345) ee abcde( ee ','(',')') --returns 12345
RETURN
WITH E1(N)
AS
(
SELECT a
FROM
(VALUES (NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
,(NULL)
) AS V([a])
)
,E2(N) AS (SELECT 1 FROM E1 a, E1 b)
,E4(N) AS (SELECT 1 FROM E2 a, E2 b)
,cteTally(N)
AS
(
SELECT TOP
(
ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)
) FROM E4
)
,cteStart(N1,idx)
AS
(
SELECT 0,0
UNION ALL
SELECT t.N+1,ROW_NUMBER()OVER(ORDER BY t.N)
FROM cteTally t
WHERE SUBSTRING(@pString,t.N,1) IN( @pDelimiterStart,@pDelimiterEnd)
)
,cteLen(lStart,lEnd,idx)
AS
(
SELECT ds.lStart+1
,de.lEnd
,s.idx
FROM cteStart s
CROSS APPLY
(
SELECT NULLIF(CHARINDEX(@pDelimiterStart,@pString,s.N1),0)
) AS ds(lStart)
CROSS APPLY
(
SELECT NULLIF(CHARINDEX(@pDelimiterEnd,@pString,ds.lStart+1),0)
) AS de(lEnd)
WHERE ds.lStart > 0 AND de.lEnd > 0
)
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.idx)
,Item = SUBSTRING(@pString, l.lStart, l.lEnd-l.lStart)
,OffsetStart = l.lStart
,OffsetEnd = l.lEnd
FROM cteLen l
WHERE l.idx & 1 = 0
; 
Comparison of the new methods of splitting strings in SQL Server 2016 to the tried and true methods
2018-06-15 (first published: 2016-04-12)
9,076 reads