|Subject||RE: [firebird-support] Re: isnumeric() in Firebird|
>[mailto:email@example.com] On Behalf Of Adam
> -----Original Message-----
> From: firstname.lastname@example.org
> Sent: Wednesday, August 02, 2006 8:16 PMThanks, that probably would've been simpler.
> To: email@example.com
> Subject: [firebird-support] Re: isnumeric() in Firebird
> --- In firstname.lastname@example.org, <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
> Bar BigInt)
> declare variable bar varchar(25);
> for select bar from foo into :bar do
> MaxBar = Cast(:Bar as BigInt);
> when any do
> Then you can do:
> select max(Bar) from GetNumericBar;
> (As always, usual disclaimers, haven't checked syntax blah blah may
> not work blah)
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
and punctuated numerics.
So I went another way and wrote a UDF named REGEXMATCH and now I can
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]