Oracle中按下划线最后索引截取子串的技术实现问题

你的问题出在原查询硬编码了要找第2个下划线的位置，当字符串里下划线数量不足2时，INSTR会返回0，导致后续的SUBSTR计算出错，最终触发NVL返回原字符串，不符合你的需求。

要实现不管有多少个下划线，都截取最后一个下划线之前的内容，我们可以利用Oracle的INSTR函数支持反向查找的特性，从字符串末尾开始定位最后一个下划线的位置，具体方案如下：

推荐解法：使用反向INSTR+CASE语句 #

SELECT 
    CASE 
        -- 先判断字符串中是否存在下划线
        WHEN INSTR(your_column, '_') > 0 THEN 
            -- 从开头截取到最后一个下划线的前一位
            SUBSTR(your_column, 1, INSTR(your_column, '_', -1, 1) - 1)
        -- 如果没有下划线，返回原字符串
        ELSE your_column
    END AS truncated_result
FROM your_table;

拆解说明： #

• INSTR(your_column, '_', -1, 1)：这个函数调用的意思是从字符串的最后一个字符开始，向前查找第1个下划线的位置，不管字符串里有多少个下划线，它都能精准定位到最后一个的位置。
• CASE语句：避免了NVL可能带来的异常情况（比如当没有下划线时，SUBSTR传入负数长度会截取错误内容），逻辑更清晰可靠。

测试验证 #

针对你的两个测试案例：

1. 测试字符串'TEMP_ABC'（仅1个下划线）：

SELECT 
    CASE 
        WHEN INSTR('TEMP_ABC', '_') > 0 THEN SUBSTR('TEMP_ABC', 1, INSTR('TEMP_ABC', '_', -1, 1) - 1)
        ELSE 'TEMP_ABC'
    END AS result
FROM DUAL;

结果：TEMP，符合你的期望。

2. 测试字符串'TEMP_ABC_XYZ'（2个下划线）：

SELECT 
    CASE 
        WHEN INSTR('TEMP_ABC_XYZ', '_') > 0 THEN SUBSTR('TEMP_ABC_XYZ', 1, INSTR('TEMP_ABC_XYZ', '_', -1, 1) - 1)
        ELSE 'TEMP_ABC_XYZ'
    END AS result
FROM DUAL;

结果：TEMP_ABC，完美匹配你的需求。

如果你想简化写法，也可以用NVL2函数（Oracle专属，比NVL更灵活）：

SELECT NVL2(INSTR(your_column, '_'), SUBSTR(your_column, 1, INSTR(your_column, '_', -1, 1)-1), your_column) FROM your_table;

NVL2(expr1, expr2, expr3)的逻辑是：如果expr1不为0/空，返回expr2，否则返回expr3，刚好适配我们的场景。

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