CODEWITHSUNDEEP
sqlsql-server-2008-r2
Randy B.
Randy B.

Reputation: 453

SQL Check constraint for an ID Number

I am trying to do a SQL Check Constraint for an ID number that is 

Name: EmployeeID
Data Type: char
Size: 10

I am using Microsoft SQL Server 2008 R2 and I am having trouble understanding how it takes some SQL commands that would work in a query, but not as a check constraint for some reason.

I am trying to do it using LIKE, such as

EmployeeID LIKE '[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]'

But it does not accept this, is there a better way to go about such a constraint?

Thank you.

Upvotes: 1

Views: 2434

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

Your constraint currently requires 9 spaces to exist. Try:

EmployeeID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Or maybe:

LEN(EmployeeID) = 10 and EmployeeID not like '%[^0-9]%'

Which says that no character is outside the group of 0-9 and that it's 10 characters in length - more maintainable if the length needs to change in future.

Upvotes: 2

rossmcbain
rossmcbain

Reputation: 129

No spaces between the square brackets and it should work:

EmployeeID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Upvotes: 3

Related Questions