Why bothering? Perhaps it’s better to keep the 3 expressions separated…
3 pattern fields bound to the same table column.
1 Like
It may be better to have 3 columns for 3 pattern types and a CHECK constraint making sure that only one of the 3 is used in a record. However, there is no pattern for character class [0-9LMNPQRSTUV]. This could be solved with a database that supports regular expressions such as HSQL 2.x and another CHECK constraint checking against the regex.
In any case, the solution will not be easy. This is not the only time that people with … uh-uh … poetic minds make life difficult for programmers and computers.
Using the right database engine with regex support, it was a matter of a 15 minutes.
Extract Regex_Check to a trusted directory. The archive contains a database document with a macro, a driver directory with one driver file (hsql 2.4.1) and a database directory. The macro is an auto-installer connecting the document with the driver and the database. Normal operatiion does not require any macro code. The macro is triggered on document open and adjusts the installation paths when the package has moved to another directory.
The database engine checks if the string entered into column “CODE” matches any of the given 2 regular expressions or if you entered a numeric string of 11 digits but not both. Any record with an invalid code will be rejected.
The input form has 2 pattern fields, one for alphanumeric code and the other one for the numeric variant. You can enter only one of the two.
EDIT: I replaced the initial link Regex_Check.zip with a better version of the same.
So, if another identifier format appears, it is enough to add a new OR REGEXP_MATCHES() and execute ALTER TABLE? Elegantly…
You have to drop the constraint and replace it with the modified one. Mostly copy and paste. You find the all the database definition code in the database/script file.
AND the new constraint must not conflict with existing data.
I found a much better solution:
alter table TBL add column NCODE CHAR(11);
update TBL set NCODE= CODE WHERE Length(CODE)=11;
alter table TBL alter column CODE SET NULL;
update TBL set CODE=Null WHERE Length(CODE)=11;
alter table TBL drop constraint REGEX_CHECK
alter table TBL alter column CODE CHAR(16);
ALTER TABLE "TBL" ADD CONSTRAINT "REGEX_CHECK" CHECK(
REGEXP_MATCHES(PUBLIC."TBL"."CODE",'^[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1}$')
OR REGEXP_MATCHES(PUBLIC."TBL"."CODE",'^[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1}$')
OR PUBLIC."TBL"."CODE" IS NULL
)
ALTER TABLE "TBL" ADD CONSTRAINT "DIGIT11_CHECK" CHECK(
REGEXP_MATCHES(PUBLIC."TBL"."NCODE",'^[0-9]{11}$')
OR PUBLIC."TBL"."NCODE" IS NULL
)
ALTER TABLE "TBL" ADD CONSTRAINT "ALTERNATE_CHECK" CHECK(
PUBLIC."TBL"."CODE" IS NULL AND NOT PUBLIC."TBL"."NCODE" IS NULL
OR NOT PUBLIC."TBL"."CODE" IS NULL AND PUBLIC."TBL"."NCODE" IS NULL
)
Now you have CODE (16 alphanumeric characters) and NCODE(11 digits).
You can enter a valid alpha-numeric string into CODE or a numeric string into NCODE but not both.
The link to the new version resulting from the above SQL script is Regex_Check.odb
Regular expressions have () capturing parentheses grouping and | OR alternation operator, refined to use the slightly more efficient (?:...) non-capturing parentheses grouping, gives one expression
^(?:[0-9]{11})|(?:[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1})|(?:[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1})$
1 Like