How to extract substrings limited with delimiters.

If you need extract from really big string small strings which are limited by delimiters, so for you can be useful this example:

DECLARE @text AS NVARCHAR (MAX) = '***[ddd]***
dfdf
fdfdfdfdfdf
***[fff]***
4545445
45454
***[ahaASSDAD]***

DFDFDF
***[SOME   TEXT
';

DECLARE @PatBegin AS VARCHAR (1000) = '***[';

DECLARE @PatEnd AS VARCHAR (1000) = ']***';

WITH   C1
AS     (SELECT 0 AS Num
        UNION ALL
        SELECT 0),
       C2
AS     (SELECT C1.Num
        FROM   C1 CROSS JOIN C1 AS C2),
       C3
AS     (SELECT C2.Num
        FROM   C2 CROSS JOIN C2 AS C3),
       C4
AS     (SELECT C3.Num
        FROM   C3 CROSS JOIN C3 AS C4),
       C5
AS     (SELECT C4.Num
        FROM   C4 CROSS JOIN C4 AS C5),
       numbers
AS     (SELECT ROW_NUMBER() OVER ( ORDER BY o.Num) AS Number
        FROM   C5 AS o),
       counts
AS     (SELECT COUNT(*) AS CNT
        FROM   numbers
        WHERE  PATINDEX(@PatEnd, @TExt) = 1),
       c
AS     (SELECT N.Number AS CHARBegin
        FROM   numbers AS N
        --CROSS JOIN numbers N2
        WHERE  SUBSTRING(@text, N.Number, LEN(@PatBegin)) = @PatBegin),
       d
AS     (SELECT c.CHARBegin,
               T.CHARBegin AS CharEnd,
               ISNULL(c.CHARBegin + LEN(@PatBegin) + NULLIF (CHARINDEX(@PatEnd, SUBSTRING(@text, c.CHARBegin + 1, ISNULL(T.CHARBegin, 2E7) - C.CHARBegin)), 0), 2e7) AS FactEnd
        FROM   c OUTER APPLY (SELECT TOP 1 C2.CHARBegin
                              FROM   C AS C2
                              WHERE  C2.CHARBegin > C.CharBegin) AS T)
SELECT SUBSTRING(@text, d.CHARBegin + LEN(@PatBegin), d.FactEnd - [d].[CHARBegin] - LEN(@PatBegin) - LEN(@PatEnd)) AS String_OUT,
       [d].CHARBegin
FROM   d;

here you have 2 delimiter - @PatBegin and @PatEnd - begin and end delimiters.
Result here will be:

UPDATE (09-01-2012)
Fixed logic to handle lack of last closing delimiter