How to convert string to number without getting any errors?
You can combine regular CAST, with error handling to achieve that.
DECLARE VARIABLE ch VARCHAR(10);
DECLARE VARIABLE nr INTEGER:
...
BEGIN
nr = CAST(ch AS INTEGER);
WHEN ANY DO nr = 0;
END
...
In this example, string 'ch' will be converted to integer. If conversion fails, 'nr' would get value zero. Make sure you use a separate BEGIN..END block for this, as WHEN works for the entire block.
With Firebird 2.5 and above you can use a regular expression to check whether the string is a valid number before converting it. You even check if number has certain digits, etc. Full explanation of the syntax can be found in Firebird 2.5 release notes:
http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-dml-regex