>
> -----Original Message-----
> From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam

> Sent: Wednesday, August 02, 2006 8:16 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: isnumeric() in Firebird
>
>
> --- In firebird-support@yahoogroups.com, <firebird@...> wrote:
> >
> > Here's a table:
> >
> > Create table foo (
> > bar varchar(25)
> > .. Other fields ...
> > );
> >
> > bar has some entries that are legal numbers and some that are not.
> I'd like
> > to find the
> > largest value for bar considering only those rows where bar is a legal
> > number. In some
> > databases, I could say this:
> >
> > Select max(bar) from foo where isnumeric(bar).
> >
> > It appears that Firebird has no isnumeric built-in. Is there
> something else
> > I could use?
> > A UDF somewhere, maybe?
>
> You could certainly write a UDF that returned the number if it was
> numeric or a really negative number otherwise (-2^63 comes to mind).
>
> Firebird is able to cast between varchar and integer, but I think you
> will get an exception if it can't occur. You could easily do a stored
> procedure that ignored that exception, something like
>
> Create or Alter procedure GetNumericBar
> returns
> (
> Bar BigInt)
> as
> declare variable bar varchar(25);
> begin
> for select bar from foo into :bar do
> begin
> begin
> MaxBar = Cast(:Bar as BigInt);
> Suspend;
> when any do
> begin
> end
> end
> end
> ^
>
> Then you can do:
>
> select max(Bar) from GetNumericBar;
>
> (As always, usual disclaimers, haven't checked syntax blah blah may
> not work blah)
>
> Adam
>

Thanks, that probably would've been simpler.

When I looked into isnumeric a little more, I realized that MS had kind of
left a lot of stuff about that built-in ambiguous, e.g., handling
exponentials
and punctuated numerics.

So I went another way and wrote a UDF named REGEXMATCH and now I can
say this:

Select max(Cast(bar as Integer) where REGEXMATCH(bar, '^[0-9]+$') = 1;


Michael D. Spence
Mockingbird Data Systems, Inc.


[Non-text portions of this message have been removed]