1

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?

TZHX
  • 5,291
  • 15
  • 47
  • 56
George
  • 39
  • 6
  • @siyual it says sqlserver 2012 in the title, so I guess T-Sql ? – P. O. Jun 05 '15 at 13:02
  • this was already discussed. see http://stackoverflow.com/questions/11223245/sql-server-regular-expression-constraint – sbiz Jun 05 '15 at 13:06

1 Answers1

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/

BrianAtkins
  • 1,279
  • 9
  • 17