1

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

Arioch 'The
  • 15,799
  • 35
  • 62
Joan Wojcicki
  • 75
  • 1
  • 7
  • Can you show us an actual query and literal error message? Perhaps one against `rdb$database` that uses SIMILAR TO ? – pilcrow Mar 16 '21 at 17:55
  • I'm using Visual Studio to connect to the firebird database and the FirebirdSql.Data.FirebirdClient reference in my project. I'm bilding a sql statement in a string builder, and then connecting to the database this way. I'd rather not disclose the field names. My pseudo code shows what I'm querying, and the quoted text is the error message I get. – Joan Wojcicki Mar 16 '21 at 18:24
  • What is Firebird version? Use TraceAPI to see queries from server viewpoint. "c:\Program Files\Firebird\Firebird_2_5\bin\fbtracemgr.exe" or IBExpert or http://FBProfiler.sf.net // replace fieldnames with AAAA, BBBB, CCCC fdifferent letters for different tokens. Query to tell FB version - http://dbfiddle.uk/?rdbms=firebird_3.0 – Arioch 'The Mar 16 '21 at 21:31
  • also what is database format? `select MON$ODS_MAJOR, MON$ODS_MINOR from MON$DATABASE` https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref-appx05-mondb – Arioch 'The Mar 16 '21 at 21:38
  • `SIMILAR TO` was introduced in Firebird 2.5, which would indicate you're using an older version. – Mark Rotteveel Mar 17 '21 at 07:30
  • I am connecting to a vendor database, and am only pulling data from tables they expose for me. I'm not able to run either of the queries that tells you the version or the format. I get a message that says table does not exist. – Joan Wojcicki Mar 17 '21 at 16:37
  • That is a contradiction. `pulling data from tables they expose for me` - there is no such thing as "publishing" or "exposing" in Firebird. All the tables that exists are visible. However your USER may be denied `select grant` on some tables, this however would make the proper error massage about access rights. If "message that says table does not exist" is generated - that that is how it is. The documentation above lets you make a query like `select * from RDB$RELATIONS where coalesce(RDB$SYSTEM_FLAG, 0) = 0` and see all the tables existing. What does the first query say then? – Arioch 'The Mar 18 '21 at 09:34
  • There are Interbase-specific and Firebird-specific non-SQL API to query server version. I don't know if you know how to call them from VB# or what is it you use. Maybe commercial www.ibprovider.com exposes some of those APIs or maybe not, i don't know. If you would have to work with the database you really better learn the exact server version. Perhaps it is already in your job description, or can be asked for. – Arioch 'The Mar 18 '21 at 09:36
  • you might also ask in Firebird ADO.Net provider support forum if there is non-SQL API to query Firebird server (not client!) version in it. The link to support forum is in the provider's tag description. – Arioch 'The Mar 18 '21 at 09:43
  • running this query: select * from RDB$RELATIONS where coalesce(RDB$SYSTEM_FLAG, 0) = 0 gives me an error that says capability not supported. Running this query: select MON$ODS_MAJOR, MON$ODS_MINOR from MON$DATABASE gives me a message table does not exist. – Joan Wojcicki Mar 18 '21 at 13:26
  • you may check release notes per every version or (in russian) on `firebirdsql.su`, from memory mon$-tables were introduced in Firebird 2.1 and `get$context` function was introduced in Firebird 2.0, and `coalesce` function in FB 1.5.3.... IOW it becomes crucial you learn (ask or investigate) for sure the exact server version! – Arioch 'The Mar 19 '21 at 12:49
  • Now are you totally sure you repeated error text verbatim??? because i do not think `capability is not supported` is FB error at all! Maybe it is Delphi version with old BDE lib or new Fair/AnyDAC but hardly FB. http://codeverge.com/embarcadero.delphi.firedac/-firedac-phys-mssql-303-capability/1090663 and https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=9819&msg=38602 /// FB is said to have "feature not supported" http://delphimaster.net/view/3-1141557640 – Arioch 'The Mar 19 '21 at 12:50

1 Answers1

2

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • I wonder if he could appear using FB 2.5 with older ODS. On sql.ru Khorsun once said this would lead to a different error message, but no one bothered to check. // that being said, he can do de facto the same function in FB 2.x: using SProcs in stead of SFuncs is a well known trick, albeit would need a load of boilerplate. – Arioch 'The Mar 17 '21 at 09:15
  • This is a great solution, Mark Rotteveel. However, I am connecting to a vendor database and I only have the ability to query the database. I'm using a Firebird dll from 2007. WIll check to see if there's an update. – Joan Wojcicki Mar 17 '21 at 16:59
  • @JoanWojcicki read "Chapter 7.3. Stored Functions" in Firebird 2.5 SQL reference, the link is there in top-level comments – Arioch 'The Mar 18 '21 at 09:40