CODEWITHSUNDEEP
sql-serverconstraints
RoflWaffle17
RoflWaffle17

Reputation: 191

Regex for MsSQL check constraint

I am trying to make a column level check constraint on a table I have. I want values being inserted into the columns to be checked and made sure they're only character.

For example values ('hello','there') would pass, but values ('h3llo','th3r3') would not.

I can get it to work for a specific amount of characters (see table below), but I want to make it dynamic.

I have tried ^[a-zA-Z]+$ as well, but that doesn't seem to work either.

The simple table layout is below.

CREATE TABLE owner
  (
     owner_id        ID IDENTITY(1, 1) PRIMARY KEY,
     owner_firstname FIRSTNAME,
     owner_lastname  LASTNAME,
     CONSTRAINT firstname_cc CHECK (owner_firstname LIKE '[a-zA-Z][a-zA-Z][a-zA-Z]'),
     CONSTRAINT lastname_cc CHECK (owner_lastname LIKE '[a-zA-Z][a-zA-Z][a-zA-Z]')
  );

Upvotes: 3

Views: 7082

Answers (1)

Martin Smith
Martin Smith

Reputation: 453648

SQL Server LIKE syntax does not accept regular expressions.

You can use check (owner_firstname not like '%[^A-Z]%').

This constraint rejects any value containing a character not in the range A-Z.

You don't need to also specify a-z except if you are on a case sensitive collation.

Upvotes: 5

Related Questions