在SSMS中提取列内文件名最后下划线后、点前的内容

针对从格式如Abc_def_ghi_contact.pdf、Asdd_dk_hk_can.pdf的字符串里，提取最后一个下划线与英文句号之间内容的需求，可通过以下SQL语句实现：

核心查询代码 #

假设你的数据表名为YourTable，存储目标字符串的列名为FileNameColumn，执行以下查询：

SELECT 
    FileNameColumn,
    SUBSTRING(
        FileNameColumn,
        -- 计算最后一个下划线之后的起始位置
        LEN(FileNameColumn) - CHARINDEX('_', REVERSE(FileNameColumn)) + 2,
        -- 计算要提取的子字符串长度
        CHARINDEX('.', FileNameColumn) - (LEN(FileNameColumn) - CHARINDEX('_', REVERSE(FileNameColumn)) + 2)
    ) AS ExtractedContent
FROM YourTable;

代码逻辑说明 #

• REVERSE(FileNameColumn)：反转目标字符串，将原字符串末尾的下划线和句号转换为从左数的匹配位置，便于定位最后一个下划线的位置。
• LEN(FileNameColumn) - CHARINDEX('_', REVERSE(FileNameColumn)) + 2：通过反转后的下划线位置，计算出原字符串中最后一个下划线的下一个字符位置，作为提取的起始点。
• CHARINDEX('.', FileNameColumn) - [起始位置]：计算起始位置到句号之间的字符数，确定要提取的子字符串长度。

可读性优化方案 #

如果觉得嵌套计算复杂，可通过CROSS APPLY拆分步骤，让逻辑更直观：

SELECT 
    t.FileNameColumn,
    SUBSTRING(t.FileNameColumn, pos.UnderlinePos + 1, pos.DotPos - pos.UnderlinePos - 1) AS ExtractedContent
FROM YourTable t
CROSS APPLY (
    SELECT 
        LEN(t.FileNameColumn) - CHARINDEX('_', REVERSE(t.FileNameColumn)) + 1 AS UnderlinePos,
        CHARINDEX('.', t.FileNameColumn) AS DotPos
) pos;

这个方案先通过CROSS APPLY预先计算出最后一个下划线的位置和句号的位置，后续提取逻辑更清晰。

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