Here you will find posts that are mostly about my work as an Oracle DBA. There may occasionally be other topics posted, but by and large this blog will be about Oracle and other geeky IT topics. Perl will likely be mentioned from time to time.

If you need to do this on 10g, no problem, as a regular expression can handle it.

Fist create some test data:

drop table number_test;

create table number_test( alphacol varchar2(20));

insert into number_test values('.5');insert into number_test values('1');insert into number_test values('2');insert into number_test values(' 3');insert into number_test values('4 ');insert into number_test values('3.14159');insert into number_test values('127.0.0.1');insert into number_test values('+34.45');insert into number_test values('-54.43');insert into number_test values('this is a test');insert into number_test values('th1s is 4 t3st');insert into number_test values('.');commit;

To make the TRANLATE() function do what is needed, a lot of data manipulation had to be done in the SQL. There is so much work being done now that it now takes nearly as long to run as does the IS_NUMBER() function, so there isn't much point in using TRANSLATE().