convert varchar2 to number in oracle

Question

Hi i have the following column

Result
______
1.5
0.27
0.25
NEGATIVE
5.33
0.15
PROBLEM
NEGATIVE
POSITIVE
POSITIVE

i need to count how many negatives are when a result smaller than 1 is Also negative when i use TO_NUMBER function i get an error ORA-01722 "invalid number"

Duplicate see > https://stackoverflow.com/questions/4486949/safe-to-number — Kris Rice, Oct 29 '19 at 12:37
How many negatives? Count them, `where result = 'NEGATIVE'`. Where does "result smaller than 1" come into the game? — Littlefoot, Oct 29 '19 at 12:39

score 1 · Answer 1 · answered Oct 29 '19 at 12:48

'NEGATIVE' is a string not a number, hence ORA-01722 when you try to turn it into a number. So you need to handle those occurrences. Something like:

select count(
           case when result = 'NEGATIVE' then 1
                when result != 'PROBLEM' and to_number(result) < 1 then 1
                else null
           end) as tot_negatives
from your_table;

Strictly speaking 0.15 is not negative because it is greater than zero, but I've implemented your definition.

Note that I've assumed you don't have any other rogue non-numeric strings in your column, beyond 'NEGATIVE' and 'PROBLEM'. If there are others, then you need to handle them too.

score 0 · Answer 2 · answered Oct 29 '19 at 13:33

On oracle version 12.2 and higher, you can use on conversion error with to_number function as following:

Select 
    Count(1) as result
From your_table
Where your_column = 'NEGATIVE' 
Or to_number(your_column default 1 on conversion error) < 1 ;

Cheers!!

convert varchar2 to number in oracle

2 Answers2