sql - 从 SQL 中的字符串中提取子字符串

Question

我需要提取一个被 ***[some text] 包围的文本字符串，如下例所示:

some text
some text
***[some text]
THIS SHOULD BE EXTRACTED
***[some text]
some text
some text
some text
some text
some text
***[some text]
THIS SHOULD BE EXTRACTED TOO
***[some text]
some text

输出应该是:

THIS SHOULD BE EXTRACTED
THIS SHOULD BE EXTRACTED TOO

我试过 PATINDEX像这里，但找不到提取字符串的方法。

PATINDEX('%[*][*][*][[]%]%%[*][*][*][[]%]%',@Text)

我期待听到任何建议。

Answer 1

对于评论中提出的更简单的案例，您可以这样做

;WITH T(C) AS
(
 SELECT '
    some text
    some text
    ***[some text 1]
    THIS SHOULD BE EXTRACTED
    ***[some text 2]
    some text
    some text
    some text
    some text
    some text
    ***[some text 1]
    THIS SHOULD BE EXTRACTED TOO
    ***[some text 2]
    some text'
)
SELECT col.value('.','varchar(max)')
FROM T
CROSS APPLY (SELECT CAST('<a keep="false">' + 
                        REPLACE(
                            REPLACE(C,'***[some text 2]','</a><a keep="false">'),
                        '***[some text 1]','</a><a keep="true">') + 
                    '</a>' AS xml) as xcol) x
CROSS APPLY xcol.nodes('/a[@keep="true"]') tab(col)