CODEWITHSUNDEEP
sqlstringoracle-databasecase
Swetosree Sinha
Swetosree Sinha

Reputation: 31

ORA-01722: invalid number - getting this error

I am trying to execute below in oracle sql developer

select code, case when (code = 'SS') then 1 else to_number(code) end as code_modified 
from pxrptuser.WBS

But I am getting error.

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

The output should be -

code     code_modified
D0DV-IMS null
gWBS     null
8        8
1        1
SS       1

Please help me with the actual query

Upvotes: 0

Views: 801

Answers (1)

GMB
GMB

Reputation: 222582

You have strings in your data that cannot be converted to numbers (other than "SS").

Starting Oracle 12.2, you can use the on conversion error clause to to_number() to return null for invalid values:

select code, 
    case 
        when code = 'SS' then 1 
        else to_number(code default null on conversion error) 
    end as code_modified 
from pxrptuser.WBS

In earlier versions, one alternative uses a regex. If your numbers have no decimal part, as showned in your data, it is simpler:

select code, 
    case 
        when code = 'SS' then 1 
        when not regexp_like(code, '\D') then to_number(code)
    end as code_modified 
from pxrptuser.WBS

Upvotes: 1

Related Questions