3

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?

TrewTzu
  • 1,110
  • 2
  • 11
  • 27
  • 1
    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 Aug 13 '12 at 05:04
  • i was following some thing i saw here. http://www.dbforums.com/microsoft-sql-server/714703-check-constraint-regular-expressions.html – TrewTzu Aug 13 '12 at 05:05
  • 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:52
  • Relevant QA on the DBA site... https://dba.stackexchange.com/q/266438/3690 – Martin Smith May 06 '20 at 21:09

1 Answers1

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

Kobi
  • 135,331
  • 41
  • 252
  • 292
  • that'll teach me for trying to make my Regex cleaner and short. Thanks Kobi – TrewTzu Aug 13 '12 at 05:29
  • 1
    @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... – Kobi Aug 13 '12 at 05:34