Subject RE: [firebird-support] Domain checks
Author Rick Debay
Are there regular expression UDFs? A google turned up match() (if it
exists) and the keyword SIMILAR.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, April 12, 2005 8:06 PM
Subject: RE: [firebird-support] Domain checks

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


Yahoo! Groups Links