CODEWITHSUNDEEP
sqloracle-database
tom_herman
tom_herman

Reputation: 11

convert varchar2 to number in oracle

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"

Upvotes: 0

Views: 382

Answers (2)

Popeye
Popeye

Reputation: 35930

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!!

Upvotes: 0

APC
APC

Reputation: 146349

'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.

Upvotes: 1

Related Questions