SQL Server中基于TEST标识提取指定字符串内容的技术求助

需求说明 #

从字符串列中提取包含固定片段TEST的完整内容：以TEST为核心，提取其左侧最近空格到右侧最近空格之间的所有字符。

解决方案 #

通过组合CHARINDEX、SUBSTRING和REVERSE函数实现，核心逻辑是定位TEST的位置，再分别找到其左右两侧最近的空格，最终截取目标片段。

以下是可直接测试的SQL代码：

DECLARE @testTable TABLE (OriginalString NVARCHAR(MAX))
INSERT INTO @testTable VALUES
('How to capture 04340-TEST-0030-dd-01 data from a string'),
('How to capture some data 8383228-TEST-0030-q1-010123 data from a string in MS SQL'),
('How to capture something 043140-TEST-0030-z9-0111 data from a string in SQL Server 2022')

SELECT 
    OriginalString,
    SUBSTRING(
        OriginalString,
        -- 定位TEST左侧最近的空格位置，无前置空格则从字符串开头截取
        LEN(SUBSTRING(OriginalString, 1, CHARINDEX('TEST', OriginalString) - 1)) 
        - CHARINDEX(' ', REVERSE(SUBSTRING(OriginalString, 1, CHARINDEX('TEST', OriginalString) - 1))) + 2,
        -- 计算截取长度：右侧空格位置 - 左侧空格位置
        CHARINDEX(' ', OriginalString, CHARINDEX('TEST', OriginalString) + 4) 
        - (LEN(SUBSTRING(OriginalString, 1, CHARINDEX('TEST', OriginalString) - 1)) 
           - CHARINDEX(' ', REVERSE(SUBSTRING(OriginalString, 1, CHARINDEX('TEST', OriginalString) - 1))) + 2) + 1
    ) AS ExtractedValue
FROM @testTable

代码拆解 #

1. CHARINDEX('TEST', OriginalString)：获取TEST在原字符串中的起始位置
2. REVERSE(SUBSTRING(..., CHARINDEX('TEST', ...)-1))：反转TEST左侧的子串，便于快速找到距离TEST最近的空格
3. CHARINDEX(' ', REVERSE(...))：在反转后的左侧子串中定位第一个空格，对应原字符串中TEST左侧最近的空格位置
4. CHARINDEX(' ', OriginalString, CHARINDEX('TEST', ...)+4)：从TEST的结束位置（+4是因为TEST长度为4）开始，查找右侧第一个空格的位置
5. SUBSTRING：根据计算出的起始位置和长度，精准截取目标字符串

执行结果 #

OriginalString                                                                 | ExtractedValue
-------------------------------------------------------------------------------|-------------------------
How to capture 04340-TEST-0030-dd-01 data from a string                        | 04340-TEST-0030-dd-01
How to capture some data 8383228-TEST-0030-q1-010123 data from a string in MS SQL | 8383228-TEST-0030-q1-010123
How to capture something 043140-TEST-0030-z9-0111 data from a string in SQL Server 2022 | 043140-TEST-0030-z9-0111

内容的提问来源于stack exchange，提问作者Richard Allison