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
So for
'123'
'abbc'
'564'
I want
123
564
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what happens if there are 2 decimal points ?
ASKER
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
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
ASKER
Is this more efficient than using a predicate like
WHERE TRIM (TRANSLATE (string1, '.0123456789', ' ') ) IS NULL
which I found on techonthenet ?