如何在SQL Server中使用SUBSTRING和CHARINDEX提取指定子串

问题翻译 #

我有类似DBName_TemplateDB_TESTDB01234_document和DBName_TemplateDB_TESTDB01234678_document这样的文本，需要提取第二个下划线（_）与最后一个下划线之间的字符串，比如得到TESTDB01234和TESTDB01234678。请问如何在SQL Server中使用SUBSTRING和CHARINDEX实现该需求？我曾尝试编写代码，但仅能从第一个下划线开始提取，代码如下：

declare @Dbname varchar(max) = '#new#-TESTDB01234_document'
select substring( LEFT(@DbName,charindex('_',@DbName)-1),charindex('TEST',@DbName),len(LEFT(@DbName,charindex('_',@DbName)))-1)

通过嵌套使用CHARINDEX定位关键位置，结合SUBSTRING即可完成需求，具体实现如下：

核心逻辑 #

1. 定位第二个下划线：先找到第一个下划线的位置，再从该位置的下一位开始查找第二个下划线的索引。
2. 定位最后一个下划线：反转字符串后找到第一个下划线的位置，再用原字符串总长度计算出原字符串中最后一个下划线的索引。
3. 截取目标内容：从第二个下划线的下一位开始，截取到最后一个下划线的前一位。

完整代码示例 #

DECLARE @DbName VARCHAR(MAX) = 'DBName_TemplateDB_TESTDB01234_document';

SELECT
    SUBSTRING(
        @DbName,
        -- 第二个下划线的下一个位置作为截取起点
        CHARINDEX('_', @DbName, CHARINDEX('_', @DbName) + 1) + 1,
        -- 计算截取长度：最后一个下划线位置 - 第二个下划线位置 - 1
        (LEN(@DbName) - CHARINDEX('_', REVERSE(@DbName)) + 1) - CHARINDEX('_', @DbName, CHARINDEX('_', @DbName) + 1) - 1
    ) AS TargetString;

代码拆解说明 #

• 定位第二个下划线：CHARINDEX('_', @DbName, CHARINDEX('_', @DbName) + 1)，第一个CHARINDEX获取第一个下划线的位置，第二个CHARINDEX从该位置+1处开始检索，得到第二个下划线的索引。
• 定位最后一个下划线：LEN(@DbName) - CHARINDEX('_', REVERSE(@DbName)) + 1，通过反转字符串找到末尾下划线的相对位置，再转换为原字符串中的绝对索引。
• 截取逻辑：SUBSTRING的起始位是第二个下划线索引+1，长度为最后一个下划线索引减去第二个下划线索引再减1，确保只截取两个下划线之间的内容。

多案例验证 #

可以批量测试不同格式的字符串，确认逻辑通用性：

DECLARE @TestTable TABLE (DbName VARCHAR(MAX));
INSERT INTO @TestTable VALUES
('DBName_TemplateDB_TESTDB01234_document'),
('DBName_TemplateDB_TESTDB01234678_document'),
('Demo_Foo_BarBaz_End');

SELECT
    DbName,
    SUBSTRING(
        DbName,
        CHARINDEX('_', DbName, CHARINDEX('_', DbName) + 1) + 1,
        (LEN(DbName) - CHARINDEX('_', REVERSE(DbName)) + 1) - CHARINDEX('_', DbName, CHARINDEX('_', DbName) + 1) - 1
    ) AS TargetString
FROM @TestTable;

执行结果：

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