0

I'm looking to create three separate check constraints for my SQL Server database to ensure that a password field constrains a minimum of one number, one uppercase character and one special character.

I consider the best way to approach is by creating separate check constrains for example I have created the following constraint to ensure that password length is a minimum of 8

(len([password]) <= (8))

Could any one suggest a way to establish the required validation.

Thanks

Matt_Johndon
  • 204
  • 1
  • 6
  • 15

3 Answers3

3

You can do this with one constraint, something like:

check password like '%[0-9]%' and password like '%[A-Z]%' and password like '%[!@#$%a^&*()-_+=.,;:'"`~]%' and len(password) >= 8

Just a note about the upper case comparison: this requires that the collation being used be case sensitive. You may need to specify an explicit COLLATE in the comparison to be sure that it is case sensitive.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The special character and number constraint work perfectly, could you think of a reason why the capital letter constraint doesn't work? – Matt_Johndon Apr 20 '14 at 19:08
  • @Matt_Johndon . . . The capital letter constraint depends on the collation of the strings involved (and the default collation for the database). You'll need to use the `COLLATE` keyword to get what you want. – Gordon Linoff Apr 20 '14 at 19:31
0

I did not know answer for your question directly, but after some research i found this methods:

1) Check if given string contains uppercase character

SELECT CASE WHEN BINARY_CHECKSUM([password]) = BINARY_CHECKSUM(LOWER([password])) THEN 0 ELSE 1 END AS DoesContainUpperCase
GO

source: http://www.sqlservercentral.com/Forums/Topic800271-338-1.aspx

2) Check if given string contains at least one special character

We can make simply statement 

IF ([password] LIKE '%[^a-zA-Z0-9]%')

source: How to detect if a string contains special characters?

I hope my answer helped you. Kind regards,

Rafal

Community
  • 1
  • 1
Rafał Czabaj
  • 720
  • 7
  • 16
0

I had to remove the 'a' in the collection of special characters and added the collate mentioned above. Thanks for your help!

([password] like '%[0-9]%' AND [password] like '%[A-Z]%' collate Latin1_General_BIN2 AND [password] like '%[!@#$%^&*()-_+=.,;:~]%' AND len([password])>=(8))
Kulshreshth K
  • 1,096
  • 1
  • 12
  • 28
dripcode
  • 31
  • 2