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

Question

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?

@siyual it says sqlserver 2012 in the title, so I guess T-Sql ? — P. O.
– P. O., Commented Jun 5, 2015 at 13:02
this was already discussed. see stackoverflow.com/questions/11223245/… — sbiz
– sbiz, Commented Jun 5, 2015 at 13:06

BrianAtkins · Accepted Answer · 2015-06-05 13:55:11Z

1

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/

edited Jun 5, 2015 at 13:55

answered Jun 5, 2015 at 13:08

BrianAtkins

1,3599 silver badges17 bronze badges

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

2 Comments

sbiz Over a year ago

I think he was asking if and how can you put regex in constraints.

sbiz Over a year ago

He needs [A-Z][A-Z][A-Z][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9]

Collectives™ on Stack Overflow

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

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