using to_number on varchar field Oracle 9i

IS there anyway to prevent an error when a fields value is not numeric. I have a column of varchars - some with numeric values and some not. I'm only interested in returning the fields that have valid numbers (converted by to_number). I am happy to have zero returned for non-numerics or for them to be excluded from the set entirely. At the moment I just get an error when to_number hits the 'abbc'
So for
'123'
'abbc'
'564'
I want
123
564
colindowAsked:
Who is Participating?
 
earth man2Commented:
create or replace function number_or_zero( str in varchar2 )  return number as
begin
   return to_number( trim( str ) );
exception
   when others then
   return 0;
end;
/

SQL> select number_or_zero(' 123 ' ) as go_1, number_or_zero( ' abbc' ) as go_2 from dual;

      GO_1       GO_2
---------- ----------
       123          0

0
 
colindowAuthor Commented:
Many thanks
Is this more efficient than using a predicate like

WHERE TRIM (TRANSLATE (string1, '.0123456789', ' ') ) IS NULL

which I found on techonthenet ?
0
 
earth man2Commented:
what happens if there are 2 decimal points ?
0
 
colindowAuthor Commented:
Not sure. My situation doesnt really involve decimal points but I guess a thorough solution would allow for this.
As I am in a position to create the function I'll go with you solution as it seems neater and probably more efficient.

Many thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.