Is there a database mechanism that allows you to query the field constraint specified by RegEx?

Question

I am looking for a database engine that can handle data restrictions specified through RegEx. Therefore, in addition to the data type, I want to be able to control the data format. For instance. a varchar(255) field may be further limited as [a-zA-Z0-9 ] .

I need RegEx to be requested as well, so I can share these restrictions throughout the n-level system to ensure that multiple levels are respected. For instance. MySQL allows you to query information_schema for metadata, and other database engines have similar methods.

Yesterday I posted a post ( MySQL Queriable Field Constraint from RegEx ) referring to the things I read but didn't look promising with MySQL, so I open it to any db engine, although I would prefer MS SQL, Oracle, DB2 or MySQL since it will be easier to sell the business.

Is there a database mechanism that allows these regular expressions to be used? If so, which one and how are restrictions set and requested?

Answer 1

In Oracle, you can specify user restrictions in which you can use functions that evaluate regexp ; eg:

 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 adfg ') 2 / 1 row created. SQL> insert into test_pattern values ('_START adfg ') 2 / insert into test_pattern values ('_START adfg ') * ERROR at line 1: ORA-02290: check constraint (SIUINTEGRA.CHECK_PATTERN) violated 

You can get information about the restrictions that you set, for example:

 select * from dba_constraints where table_name = 'TEST_PATTERN' 

Answer 2

Here is an example template in sql server for this.

 create table #Something ( SomeValue varchar(255) , constraint MyCheck CHECK (SomeValue like '[az][az][0-9]%') ) insert #Something select 'ab3adoofnod' --(1 row(s) affected) insert #Something select 'a3b3adoofnod' Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "MyCheck". The conflict occurred in database "tempdb", table "dbo.#Something__________________________________________________________________________________________________________0000000000DD", column 'SomeValue'. The statement has been terminated. 

If you want to use some t-sql to view the definitions of your test constraints, you can use the sys.check_constraints directory view.

Here is an example of viewing ALL test constraints for the table above. The definition columns will search for wildcards as defined in the restriction.

 select * from tempdb.sys.check_constraints where parent_object_id = object_id('tempdb..#Something')