Regex CHECK constraint not working with SQL server

Question

im trying to reject all inputs not in the format "03 xxxx xxxx" so i created a table like

create table records
(
....
num varchar(255) NOT NULL,
...
CONSTRAINT num_check CHECK (num like '03 [0-9]{4} [0-9]{4}')
)

which should (i think?) accept for example "03 1234 1234". but if i try to add this via sql manager i get an error with the message: "the INSERT statement conflicted with the CHECK constraint "num_check" "

at first i thought my Regex was off but ive tried it in a few other places and it accepts the example above. any ideas?

As far as I know you can't implement Regex in SQL Server without using a CLR assembly. It doesn't have native Regex support like Oracle does...unless my memory has failed me. — scarpacci
– scarpacci, Commented Aug 13, 2012 at 5:04
i was following some thing i saw here. dbforums.com/microsoft-sql-server/… — TrewTzu
– TrewTzu, Commented Aug 13, 2012 at 5:05
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:52
Relevant QA on the DBA site... dba.stackexchange.com/q/266438/3690 — Martin Smith
– Martin Smith, Commented May 6, 2020 at 21:09

Kobi · Accepted Answer · 2012-08-13 05:41:24Z

7

like does not work with regular expressions, it has its own, much simpler wildcard patterns, which only support %, _ , [a-z], and [^a-z]. That's it. {4} would not works, just like most regex features.
You should be able to use:

like '03 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'

Another option, a little less repetitive:

declare @digitChar nvarchar(12)
set @digitChar = '[0-9]'

Where clause:

like '03 ' + replicate(@digitChar,4) + ' ' + replicate(@digitChar,4)

Example: http://sqlfiddle.com/#!3/d41d8/3251

edited Aug 13, 2012 at 5:41

answered Aug 13, 2012 at 5:23

Kobi

139k41 gold badges259 silver badges302 bronze badges

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

2 Comments

TrewTzu Over a year ago

that'll teach me for trying to make my Regex cleaner and short. Thanks Kobi

Kobi Over a year ago

@TrewTzu - That's exactly the problem - like does not handle regular expressions - it's just the [] that is similar. Anyway, I've included another version - I did something very similar when checking for GUIDs, and didn't want [0-9a-fA-F] repeated 32 times...

Collectives™ on Stack Overflow

Regex CHECK constraint not working with SQL server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related