EmbeddedSplit8K

Carlo Romagnano, 2018-11-29 (first published: 2018-11-23)

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
;

Rate

Share

Share

Rate

Related content