Check for is numeric in Firebird database

Question

How can I check if the value of a field is numeric or not in a Firebird database? This seems like an easy problem but I can't find anything in the FreeUDFLib or standard Firebird library. Here's how I would do it in SQL Server.

Select field
from table
where isnumeric(field) = 1

Some research got me to "similar to". This should work, but throws an error:

select field
from table
where field SIMILAR TO '[0-9]+'

{"Dynamic SQL Error" & vbCrLf & "SQL error code = -104" & vbCrLf & "Token unknown - " & vbCrLf & "SIMILAR"}

Answer 1

Firebird has no built-in function that does what isnumeric from SQL Server does. In Firebird 3 you can build one yourself in PSQL, for example:

create function isnumeric(val varchar(30))
  returns boolean
  deterministic
as
  declare dblval double precision;
begin
  dblval = cast(val as double precision);
  return true;
  when any do 
    return false;
end

As to your problem with similar to, it was introduced in Firebird 2.5, so this would indicate you're using Firebird 2.1 or earlier. In that case, it is really time to update. Firebird 2.5 and older are End-of-Life and no longer receive updates (including security fixes!).