Re: Finding non numeric values - sql

"david.garrett" <david.garrett_at_attbi.com> writes:

> I'm trying to find a way to determine if a string holds any non numeric
> characters.
> For instance, I need to use a Decode to do one thing if a string like
> "6706A657987"
> has any non numeric characters and do another if it does not. In this
> example it has an "A" so it would need to be handled differently than say
> "6706657987". I played with Translate a little but it didn't really do the
> trick. Any suggestions?
>
> Any help is greatly appreciated.
>

I've had to do something similar to this where I needed to confirm the value in a varchar2 field was a numeric value. While I'm not sure if this would be considered best practice, my "simple" solution was to use a plsql function which attempts to convert the string to a number within a block with an exception handler to catch any format exceptions. If to_number was able to convert the value, the function returned true and if a format exception was raised it returned false.

Other techniques I've seen have used the instr and substr functions to loop through the field and check each character or the translate function which is used to translate all numeric values to some value and check to see if the resulting string only contains that value.

Tim Received on Wed Nov 20 2002 - 16:09:35 CST