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;

---

 

A table is created ...

create table safe_to_number_t (
  txt varchar2(10)
);
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' );

select to_number(
  regexp_substr(txt,'^[-]?[[:digit:]]*\.?[[:digit:]]*$')
) the_number,
  txt
from safe_to_number_t;

http://www.adp-gmbh.ch/ora/sql/examples/save_to_number.html