--- 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