ORA-01722:无效编号01722.00000 -“无效号码”*原因:指定号码无效。*行动:指定有效号码

Question

我是Oracle SQL Developer的新手，今天我正在运行以下内容

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,
cast(substr (it.remark, instr(it.remark,'-')+1,3 ) 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 ) and sp.label_id = cast(substr (it.remark, instr(it.remark,'-')+1,3 ) 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'
order by sp.id, it.id;

它发现了错误：

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

我认为问题在于第3行(cast(substr (it.remark, instr(it.remark,'-')+1,3 ) as integer))中的抽取，在第3行中，我需要使用cast将字符串转换为数字。

根据doc，当尝试将字符串转换为数字而字符串不能转换为有效数字时，就会发生错误。

所以，我试着替换：

cast(substr (it.remark, instr(it.remark,'-')+1,3 ) as integer)

使用

to_number(substr (it.remark, instr(it.remark,'-')+1,3 ))

甚至尝试过to_char，但都没有成功。但是，在沙箱数据库中，原始脚本似乎工作得很好。我想知道为什么会这样。任何帮助都是非常感谢的。

更新：

样本数据it

     ID    DATE       NAME     GROUP     REMARK                              COMMENT ... 
     100   20-10-08   AABC     X         ACS LOCATION 1 - ABC IDD x105213-1    
     ​101   20-10-08   AxB      Y         MN  LOCATION 8 - ABC IDD x105244-2 
     ...

样本数据sp

     ID       DATE       NAME     GROUP     label_id   
     105213   20-10-08   AABC     X         1   
     ​105244   20-10-08   AxB      Y         2
     ...

结果表明，这个错误是由-中的2 remark引起的，这导致了歧义，我只需要第二个。

新问题：

如何提取值中的最后一个 -，以便与另一列中的另一个值连接？

Answer 1

使用cast和default null on conversion error可以避免异常，并调查转换失败的原因。

示例

with dt as 
(select '001' remark from dual union all
 select '  2' from dual union all
 select 'OMG' from dual)
select substr(remark,1,3) txt,
cast (substr(remark,1,3) as INT default null on conversion error) num
from dt;

TXT        NUM
--- ----------
001          1
  2          2
OMG