Oracle执行SQL报ORA-01722错误，如何提取remark字段最后一个'-'后内容

核心问题 #

你遇到的报错是因为instr(it.remark,'-')默认正向匹配第一个-字符，而remark字段存在多个-，导致截取的子串包含非数字内容，转整数失败。你需要的最后一个-后的数字可以通过调整instr的查找参数实现。

解决代码 #

Oracle的instr函数支持传入负数值作为起始查找位置，从字符串末尾反向查找第一个匹配的字符，正好对应你要的最后一个-的位置，修改后的完整SQL如下：

select r.id, r.date,  it.group, it.comment, it.item, it.remark, r.summary,
substr (it.remark, instr(it.remark,'ABC')+8,7 )  as label1,
-- 调整instr参数，从末尾倒序找'-'，不指定截取长度默认拿到末尾所有内容
cast(substr (it.remark, instr(it.remark,'-',-1)+1) as integer) as label2
from it_table it 

inner join sp_table sp 
  on sp.id = substr (it.remark, instr(it.remark,'ABC')+8,7 ) 
  -- 关联条件同步调整instr参数
  and sp.label_id = cast(substr (it.remark, instr(it.remark,'-',-1)+1) as integer) 
inner join sq_table sq on sq.id = sp.id
where it.date > '01-jan-2020' and it.remark like '%ABC%' and it.group= 'O'
-- 可选：12c以上版本加下面这行过滤转换失败的脏数据，避免再报错
-- and validate_conversion(substr (it.remark, instr(it.remark,'-',-1)+1) as integer) = 1
order by sp.id, it.id;

补充说明 #

• instr(it.remark,'-',-1)的第三个参数-1代表从字符串最后一位开始反向查找，返回第一个匹配到的-的下标，也就是整段文本里最后一个-的位置
• 去掉substr的第三个长度参数后，会自动截取从起始位置到字符串末尾的所有内容，不用固定3位长度，适配数字位数变化的场景
• 如果你使用的是Oracle 12c及以上版本，可以在where条件中加上validate_conversion的校验逻辑，自动过滤掉remark最后一段不是数字的脏数据，彻底避免该报错。

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