sql server - Is There A Database Engine that Allows for Queriable Field Constraint Specified by RegEx?

Question

I'm looking for a database engine that can handle data constraints specified via RegEx. So in addition to the datatype, I want to be able to control the format of the data. E.g. a varchar(255) field could be further restrained to be like [a-zA-Z0-9 ].

I need the RegEx to be able to be queried too, so I can share those constraints throughout the n-tier system to enforce on several levels. E.g. MySQL allows for querying of information_schema to get meta data, and other database engines have similar ways.

I did a post yesterday (MySQL Queriable Field Constraint by RegEx), referencing things I read, but doesn't look promising with MySQL, so I'm opening this up to any db engine, although I would prefer MS SQL, Oracle, DB2 or MySQL, as it'll be easier to sell the business on.

Is there a database engine out there that allows for these regex restrictions? If so, which one is it and how do the constraints get set and queried?

Answer 1

In Oracle you can specify custom constraints, in which you can use functions that evaluate regexp; for example:

SQL> create table test_pattern ( txt varchar2(1000))
  2  /

Table created.

SQL> alter table test_pattern add constraint check_pattern check (regexp_instr(txt, '^START') != 0)
  2  /

Table altered.

SQL> insert into test_pattern values ('START a d f  g ')
  2  /

1 row created.

SQL> insert into test_pattern values ('_START a d f  g ')
  2  /
insert into test_pattern values ('_START a d f  g ')
*
ERROR at line 1:
ORA-02290: check constraint (SIUINTEGRA.CHECK_PATTERN) violated

You can get informations on constraints you set with something like:

select *
from dba_constraints       
where table_name = 'TEST_PATTERN'