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

Question

I am new to Oracle SQL Developer, and today while running this

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;

it caught the error:

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

I think the problem lies with the extraction as in row 3 (cast(substr (it.remark, instr(it.remark,'-')+1,3 ) as integer)), where I need to convert a string into a number using cast.

According to doc, the error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number.

So, I tried replacing:

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

with

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

and even tried to_char but didn't work. However, the original script seems to work fine in sandbox database. I am wondering why this is happening. Any help is greatly appreciated.

---

Update:

Sample data 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 
     ...

Sample data sp:

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

It turns out that the error was caused by having 2 - in remark which lead to ambiguity and I just need the second one.

New question then:

How do I extract the last - in the value to join with another value in the other column?

Answer 1

Use cast with default null on conversion error to avoid exception and investigate the cause of the failed conversion.

Example

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