Are regex check constraints possible in SQL Server

Question

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.

So far, I've only found one DBE that supports posix regex constraints and it is Oracle (Reference: stackoverflow.com/questions/35157662/…). 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
– James Oravec, Commented Feb 2, 2016 at 16:54
Relevant QA on the DBA site... dba.stackexchange.com/q/266438/3690 — Martin Smith
– Martin Smith, Commented May 6, 2020 at 21:08

Damien_The_Unbeliever · Accepted Answer · 2011-06-08 09:43:32Z

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).

edited Jun 8, 2011 at 9:43

answered Jun 8, 2011 at 9:24

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alan Burstein · Accepted Answer · 2013-09-13 01:44:55Z

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

answered Sep 13, 2013 at 1:44

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

Collectives™ on Stack Overflow

Are regex check constraints possible in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related