René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Converting text to numbers

A table is created ... 
create table safe_to_number_t (
  txt varchar2(10)
);
... and filled with text of which some can be converted to numbers: 
insert into safe_to_number_t values ('1.0'   );
insert into safe_to_number_t values ('1'     );
insert into safe_to_number_t values ('22.9'  );
insert into safe_to_number_t values ('FOO'   );
insert into safe_to_number_t values ('-8.99' );
insert into safe_to_number_t values ('-.22'  );
insert into safe_to_number_t values ('-BAR'  );
insert into safe_to_number_t values ('23-45' );
However, some txts cannot be converted... 
select to_number(txt) from safe_to_number_t;
... and an error is thrown: 
ERROR:
ORA-01722: invalid number
So, a function is created that can safely convert strings to numbers. When the string is inconvertable, it returns null: 
create function safe_to_number(txt in varchar2) return number is 
begin
  return to_number(txt);
exception when value_error then
  return null; 
end safe_to_number;
/

select safe_to_number(txt), txt from safe_to_number_t;

SAFE_TO_NUMBER(TXT) TXT
------------------- ----------
                  1 1.0
                  1 1
               22.9 22.9
                    FOO
              -8.99 -8.99
               -.22 -.22
                    -BAR
                    23-45

select safe_to_number(txt), txt from safe_to_number_t;

With regexp_substr

In 10g, it's possible to use regexp_substr for the same effect: 
select to_number(
  regexp_substr(txt,'^[-]?[[:digit:]]*\.?[[:digit:]]*$')
) the_number,
  txt
from safe_to_number_t;