Subject RE: [firebird-support] Domain checks
Author Helen Borrie
At 05:36 PM 12/04/2005 -0400, you wrote:

>I'm looking to filter out anything not in:
>Numeric_only [0-9]
>Alpha_only [A-Z]
>Alphanumeric_only [0-9] or [A-Z]

You couldn't do this as at all without using UDFs. If you wanted to do
this validation in a CHECK constraint, you'd need to write a custom UDF to
validate the whole string, since you have to iterate through the string
testing each character.

You could write a SP that could be called by triggers to do the same thing.
This SP would need to use UDFs. In IB_UDF, the function Ascii_val returns
the ascii decimal code of a character, while Ascii_char returns the
character, given the decimal code. You can manipulate the input string
using the internal function SUBSTRING(), without having to resort to
further UDFs. (There are several examples of this in the book).

Obviously you can't do this at domain level, since Firebird doesn't support
domain triggers. However, it's still a whole lot better for overall
integrity to do it on the server side, rather than to rely on applications
to do it. You don't have any control over applications other people might
write to access the database.

Logically it's much safer to filter IN than filter OUT. NOT expressions
don't always give you the exact opposite of the positive result.

Numeric_only [0-9] ascii_val (charval) between 48 and 57
Alpha_only [A-Z] ascii_val (charval) between 65 and 90
Alphanumeric_only [0-9] or [A-Z] use both expressions and OR them