Oracle SUBSTR函数提取变长文件名疫苗名称遇问题，如何解决？

问题背景 #

需要从Oracle数据库的文件名字段中提取疫苗名称，规则是：

• 提取下划线之后、句点之前的内容（例如从4212406_Meningitis.jpg中提取Meningitis）
• 需兼容不同长度的扩展名（如.jpg、.jpeg）及大小写扩展名（如.PNG）

当前使用的SQL无法正确去除扩展名，语句如下：

SELECT original_field, 
       SUBSTR(original_field, INSTR(original_field, '_') + 1, INSTR(original_field, '.') -1) AS current_field
FROM my_table 

原始字段示例：

4212406_Meningitis.jpg
4824729_Hep-B.jpg
3612290_Hep-B.jpg
2811504_Covid-19.jpeg
621980_Covid-19.pdf
5258652_MMR.jpeg
5755663_Meningitis.png
2555841_Covid-19.PNG
2677160_MMR.jpg
2294961_MMR.jpg

问题原因 #

SUBSTR函数的第三个参数是截取长度，而非结束位置。当前语句中INSTR(original_field, '.') -1是从字符串开头到句点的位置减1，不是从下划线后起始位置到句点的长度，导致截取内容包含部分扩展名。

解决方案 #

方案1：修正INSTR计算逻辑 #

通过计算正确的截取长度实现：

SELECT original_field,
       SUBSTR(original_field, 
              INSTR(original_field, '_') + 1, 
              INSTR(original_field, '.') - INSTR(original_field, '_') - 1) AS vaccine_name
FROM my_table;

• INSTR(original_field, '_') + 1：定位疫苗名称的起始位置（下划线后第一个字符）
• INSTR(original_field, '.') - INSTR(original_field, '_') - 1：计算截取长度，即句点位置与下划线位置的差值减1，确保只取到句点前的内容

方案2：使用正则表达式（REGEXP_SUBSTR） #

正则表达式更简洁，还能兼容复杂文件名场景（如文件名含多个句点）：

SELECT original_field,
       REGEXP_SUBSTR(original_field, '_(.*)\.[^\.]+$', 1, 1, NULL, 1) AS vaccine_name
FROM my_table;

• 正则模式'_(.*)\.[^\.]+$'：
  • _：匹配下划线
  • (.*)：捕获下划线后到最后一个句点前的所有内容（即疫苗名称）
  • \.[^\.]+$：匹配最后一个句点及后续的扩展名（确保只截取到最后一个句点前的内容）
• 最后一个参数1：返回第一个捕获组的内容，也就是目标疫苗名称

如果文件名格式固定（仅含一个下划线和一个句点），可以用更简化的正则：

SELECT original_field,
       REGEXP_SUBSTR(original_field, '_(.*)\.', 1, 1, NULL, 1) AS vaccine_name
FROM my_table;

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