Re: check if numeric .

["Ivory, Wayne" <Wayne.Ivory@ILUKA.COM>]

Just an idea, but if to_number exhibits the behaviour described why not take
advantage of it.  Use to_number to turn it into a number (of some sort) and
then use to_char to turn it back into a string.  If the result is the same
as the original then it must be numeric.

I'm talking from a theoretical standpoint here - there may be edit masks or
whatnot involved - but I think the theory is sound.

Wayne Ivory
Information Services
Iluka Resources Limited


-----Original Message-----
From: Dray, Adam [mailto:Adam.Dray@PHH.COM]
Sent: Saturday, 29 January 2000 06:21
To: SQR-USERS@list.iex.net
Subject: Re: check if numeric .


David Donnelly <Dave@isisbio.com> said:
>
> First of all, to_number is pretty much worthless, because it stops
> when if finds something it doesn't like.  For example, to_number(123junk)
> will return 123.  I think that to_number (123.45.67) would return 123.45,
> but I haven't tried this.
...
> The most effective, sure way is to use the database to convert it, and
> catch the error if it fails. However, performance-wise, this stinks if
> you have to do it many times.

Be warned, your database may exhibit the same behavior as SQR's
to_number().  I tried convert(numeric(10,4), '123junk') on Sybase SQL
Server 11.9 and got 123.0000 back.

It's too bad SQR doesn't have full (Perl style) regular expression support;
if it did, writing a numeric format checker would be a snap.
-------------------------------------------------------------------------