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