如何编写SQL查询从文件路径中提取文档修订号

核心逻辑 #

提取规则严格匹配需求：先从文件路径中取出带扩展名的文件名，去掉扩展名后截取最后一个短横线-后的所有内容，可兼容1位/2位修订号、短横线占位符的提取场景。

各数据库实现代码 #

MySQL #

SELECT
    文档名称,
    文件路径,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(SUBSTRING_INDEX(文件路径, '/', -1), '.', 1),
        '-',
        -1
    ) AS 修订号
FROM document;

PostgreSQL #

SELECT
    文档名称,
    文件路径,
    SPLIT_PART(
        SPLIT_PART(SPLIT_PART(文件路径, '/', ARRAY_LENGTH(STRING_TO_ARRAY(文件路径, '/'), 1)), '.', 1),
        '-',
        -1
    ) AS 修订号
FROM document;

SQL Server #

SELECT
    文档名称,
    文件路径,
    RIGHT(
        LEFT(
            RIGHT(文件路径, CHARINDEX('/', REVERSE(文件路径)) - 1),
            CHARINDEX('.', RIGHT(文件路径, CHARINDEX('/', REVERSE(文件路径)) - 1)) - 1
        ),
        CHARINDEX('-', REVERSE(LEFT(
            RIGHT(文件路径, CHARINDEX('/', REVERSE(文件路径)) - 1),
            CHARINDEX('.', RIGHT(文件路径, CHARINDEX('/', REVERSE(文件路径)) - 1)) - 1
        ))) - 1
    ) AS 修订号
FROM document;

补充说明 #

• 如果使用Windows格式的文件路径（分隔符为\），只需将上述SQL中的路径分隔符/替换为\即可正常使用。
• 测试验证效果如下：

  文件路径	提取结果
  /docs/项目验收报告-V2.docx	V2
  /data/操作指引-B.pdf	B
  /public/需求初稿-.docx	-

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