Blog

Sometimes the functions exposed by firebird are not enough to satisfy our needs. Luckily we can create our own internal functions.

Let’s say you want to cast a potential numeric string into Integer. If you run the internal cast() method you will see this error

SELECT CAST('12A' as Integer) FROM RDB$DATABASE

Now let’s say we want to deal with this errors. If the value is not valid, we want to cast them to null.

The only way you have is to create your own custom internal function or verify the value before attempting to cast.

Creating an internal function:

/* VERSION 001 29/11/2019 */
CREATE OR ALTER FUNCTION TRY_CAST_TO_INT(PARAM VARCHAR(32000))
  RETURNS INT
AS
BEGIN
  RETURN CAST(PARAM AS INT);
WHEN ANY DO
  RETURN NULL;
END

SELECT try_cast_to_int('12a') as C FROM RDB$DATABASE;  --> C = NULL
SELECT try_cast_to_int('12') as C FROM RDB$DATABASE;  --> C = 12

Pre-validating the data using regex

SELECT IIF('14' SIMILAR TO '[[:DIGIT:]]+', CAST('14' as Integer), null) FROM RDB$DATABASE

And that’s all.

Have fun.

(Firebird Version <= 3.0.5)