3

I am trying to find a way to validate data in my columns using regex CHECK constraints but have so far only come across the LIKE keyword that has an extremely limited subset of regex syntax. I'd like to know if there is a way to use regex in check constraints?

I am using SQL Server 2005 and 2008.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tjaart
  • 3,912
  • 2
  • 37
  • 61
  • 1
    So far, I've only found one DBE that supports posix regex constraints and it is Oracle (Reference: http://stackoverflow.com/questions/35157662/is-there-a-database-engine-that-allows-for-queriable-field-constraint-specified). MS SQL and DB2 look like they have their own wildcard variant, which is unfortunate as you loose consistency of constraints between layers :( – James Oravec Feb 02 '16 at 16:54
  • 1
    Relevant QA on the DBA site... https://dba.stackexchange.com/q/266438/3690 – Martin Smith May 06 '20 at 21:08

2 Answers2

4

I think you can create a CLR scalar UDF which can use the .Net framework RegEx classes, and then call that from your check constraint.

In fact, this article discusses this scenario (search for "Check Constraint", then move backwards through the article to find implementation details).

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

I'm a little late to the thread here but check out my article about how to do this:

http://xmlsqlninja.blogspot.com/2013/09/mdqregex-clr-functions-part-3-regex.html

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18