Link to home
Start Free TrialLog in
Avatar of colindow
colindow

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of earth man2
earth man2
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of colindow
colindow

ASKER

Many thanks
Is this more efficient than using a predicate like

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

which I found on techonthenet ?
what happens if there are 2 decimal points ?
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