CODEWITHSUNDEEP
sqlsql-serverregexconstraints
George
George

Reputation: 59

How to check attribute in SQL Server 2012 using regular expressions?

I need to insert an equipment model code, is a string formatted as:

AAA-0123456

It has to be, 3 uppercase letters, the "-" in the middle and 6 numbers, I need to have a constraint check (model code like regular expression) I think.

How can I do this?

Upvotes: 1

Views: 426

Answers (1)

BrianAtkins
BrianAtkins

Reputation: 1349

You would do this via a check constraint, see here: http://www.w3schools.com/sql/sql_check.asp

Yes you can put regex in a check constraint, here is an example:

CREATE TABLE dbo.PEOPLE (
name VARCHAR(25) NOT NULL
, emailaddress VARCHAR(255) NOT NULL
, CONSTRAINT PEOPLE_ck_validemailaddress CHECK ( dbo.RegExValidate( emailaddress, N'^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$' ) = 1 )
)

Your regular expression would be: [A-Z][A-Z][A-Z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]

Here is a great tool to build and test reg expr http://www.regexr.com/

Upvotes: 1

Related Questions