0

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?

Community
  • 1
  • 1
James Oravec
  • 19,579
  • 27
  • 94
  • 160
  • Have you tried even a little bit of research? Oracle has RegEx, sql server has a limited wildcard pattern. Either of those would work for your simple example. – Sean Lange Feb 02 '16 at 15:51
  • References for Sean's comments... MS SQL only has limited regex support (https://msdn.microsoft.com/en-us/library/ms179859.aspx) and oracle's is more extensive (https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm) – James Oravec Feb 02 '16 at 16:18
  • Still looking into DB2... So far it looks like DB2 does a wild card like MS SQL, looking to see if they support something more in depth (reference: http://stackoverflow.com/questions/19154453/how-to-write-a-query-to-ensure-email-contains) – James Oravec Feb 02 '16 at 16:22
  • PostgreSQL has strong regexp support including check constraints and indexing – Neil McGuigan Feb 02 '16 at 20:50

2 Answers2

3

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'
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • What is the output of your constraints query? Also is there a particular version I need to use to get this functionality? – James Oravec Feb 02 '16 at 16:09
  • 1
    It gives all the informations about constraints (table, schema, if they are deferred, deferrable, the check condition, ...). It works on Oracle 10g and above; previous versions do not support regexp – Aleksej Feb 02 '16 at 16:14
0

Here is a wildcard example in sql server for this.

create table #Something
(
    SomeValue varchar(255)
    , constraint MyCheck CHECK (SomeValue like '[a-z][a-z][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 check constraints you can use the sys.check_constraints catalog view.

Here is an example to view ALL the check constraints for the table above. The definition columns will provide the wildcard searching as defined in the constraint.

select * 
from tempdb.sys.check_constraints
where parent_object_id = object_id('tempdb..#Something')
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • How do you query the regex in the check? – James Oravec Feb 02 '16 at 16:06
  • Not sure I follow your question. The check constraint says the value must have 2 characters followed by a number. – Sean Lange Feb 02 '16 at 16:21
  • Part of the question is how to query the meta data of the constraint. In MySQL you can query the `information_schema` table to get meta data of the column. How do you query the MS SQL DBE to get the meta data of `[a-z][a-z][0-9]%` of this column/field? – James Oravec Feb 02 '16 at 16:32
  • You really should try to look things up. https://msdn.microsoft.com/en-us/library/ms187388.aspx – Sean Lange Feb 02 '16 at 16:50
  • The intent was to help you write a complete answer, so people would vote for your answer. Having partial answers then googling is a waste for others with similar questions. Furthermore, links with MS tend to get outdated with time too, so having a complete history/answer in stack overflow is one of the benefits of stack overflow... if you update your answer to be complete, I'll give you an up vote :) – James Oravec Feb 02 '16 at 17:02
  • LOL. You didn't ask about being to view the check constraint. Not something many people do all that frequently. Sorry if I came across as snarky. :D – Sean Lange Feb 02 '16 at 17:09
  • in tsql you can use XML Schema with a pattern constraint containing the regex and add a check constraint so inserts/updates are validated or create your own CLR function, take a look at https://dba.stackexchange.com/questions/266438/how-can-i-enforce-that-values-in-a-table-column-match-the-regular-expression-a – Krzysztof Gapski Jan 22 '21 at 07:40