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"}
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!).