Oracle. Convert varchar value "40.00" to numerical.

Question

For example, a have varchar value "40.00" and want to use it with operators ">" or "<" in where clause. How can I use it?

Answer 1

You could convert the varchar to a decimal like:

select *
from YourTable
where CAST(YourField AS decimal) < 40.00

Or use the TO_NUMBER() function:

select *
from YourTable
where TO_NUMBER(YourField) < 40.00

If the field is not always a number, and you have a relatively recent Oracle installation, you can select rows where YourField is numeric like:

select *
from (
    select * 
    from YourTable
    where regexp_like(YourField, '^-?[[:digit:],.]+$')
) sub
where TO_NUMBER(YourField) < 40.00

Answer 2

You can convert it to a number using the TO_NUMBER function:

WHERE TO_NUMBER(col) > 39

But beware: if any row in the table has a non-numeric entry in that column, the query may fail with an error:

SQL> create table t (col varchar2(10));

Table created.

SQL> insert into t values ('39.00');

1 row created.

SQL> insert into t values ('40.00');

1 row created.

SQL> insert into t values ('41.00');

1 row created.

SQL> insert into t values ('xxx');

1 row created.

SQL> select * from t where to_number(col) > 39;
ERROR:
ORA-01722: invalid number

Answer 3

If you want to compare them as numbers then use the TO_NUMBER function Oracle 11g doc

I would suggest that if you want to treat thus as a number the best way is to change your schema/DDL so that the 40.00 is stored in a number field. This would mean that comparisons work , access be more efficient e.g. where field > x would use an index - where TO_NUMBER(field) > x does not use an index and data errors can be caught on insert rather than having to run reports to find non numeric data.