In case by 'Access' the OP means they are writing engine-level
constraints (and they should be) then you can only do basic pattern
matching, however this will get you quite far. See:
Using Wildcard Characters in String Comparisons
http://office.microsoft.com/en-us/assistance/HP010322841033.aspx
I always feel that with regex the ultimate aim is to write a 'one line'
solution i.e. a single expression to that validates all possible
values. This can be a maintenance issue e.g. three weeks later I
revisit my 'one size fits all' solution because it's failing values
that it should be passing and I have to effectively figure out anew
what it does (because I've forgotten) before I can fix the problem.
With database constraints, in some ways it's 'the more the merrier'
i.e. if I write ten constrains, ideally with meaningful names, that
essentially each validate one rule (e.g. "the character must be the
letter H") then its easier to debug the code later if I can go straight
to a single constraint (e.g. the error message refers to a constraint
named building_ref_nbr__must_start_with_letter_H).
So using multiple rules, rather than one big rule, can get around a lot
of the limitations of basic pattern matching available.
Another thing to note is that in SQL some things you get 'for free'
e.g. if the rule is "exactly ten alpha characters" then the "exactly
ten characters" part can be accommodated by choosing a fixed text data
type and then rule in effect becomes "does not contain non-alpha
characters" e.g.
CREATE TABLE Buildings (
building_ref_nbr CHAR(12) NOT NULL,
CONSTRAINT
building_ref_bnr__alpha_characters_only
CHECK (building_ref_nbr NOT LIKE '%[!A-Z]%')
....
);
HTH,
Jamie.
--
