Re: Finding non numeric values - sql

One big problem with the translate/replace/substr/instr approach is that it only works for the simplest cases. If your definition of a valid number is a simple unsigned integer then it would work, but any signed or fractional numbers introduce positional validation i.e. this string is a valid number: +1234.56 but this is not: 12+3.4.5-6
then there's scientific notation: +6.2E-23 is numeric too.

I think on balance I'd go with the "TO_NUMBER/handle the exceptions approach" unless I'm working with the simplest case.

As for the OP's specific question, the use of string functions feels like the right approach.

Martin Doherty

Tim Cross wrote:

>"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 - 17:40:54 CST