SQL 2008r2 check constraint question 2

[wbodger]

I was not sure of this question would go here or Setup and Administration, but here goes. I need to put constraints on two columns in my db, but am a little unclear as to the specifics of achieving what I need. For one field I need to constrain the first character to a letter, then any letter or number or underscore, no spaces and all letters must be uppercase. the other is actually very close, just no uppercase constraint. And my installation is case insensitive.

Thanks!

wb

 

[gmmastros]

Alter 
Table   YourTableName 
With    nocheck
add     constraint YourConstraintName
check   (
        PatIndex('%[^A-Z0-9_]%', YourColumnName COLLATE Latin1_General_BIN) = 0 
        And PatIndex('[^A-Z]%',  YourColumnName COLLATE Latin1_General_BIN) = 0
        )

Basically, there are two conditions here. One checks for and character [!]not[/![ A-Z0-9_ anywhere in the string. The other condition makes sure that the first character is A-Z (by actually checking for the first character not equal to A-Z.

Now... please understand that this is another "attempt" at implementing your rules. I do not guarantee that this is what you want. This is why I posted yesterday with a table of dummy data that had multiple rows. As you can see, I simply copied the 2 PatIndex function calls in to the constraint.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Looks like George has cracked it again!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[wbodger]

OK, that makes sense. I was playing around a bit with it yesterday and couldn't see how to run two Patindex matches. I will give this idea a shot!

 

[wbodger]

That did it, thanks again!