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.
(Firebird Version <= 3.0.5)