Search code examples
sqlsql-servercheck-constraint

Password check-constraint SQL server

I'm trying to create a password constraint that must contain:

-at least 1 Upper case

-at least 1 number

-at least 5 characters long

I have searched a lot and i just can't make it work

for example, i have tried this

(len([PASSWORD])>(5) AND [PASSWORD] like '%[0-9]%' AND [PASSWORD] like '%[A-Z]%')

now, it will accept it if the password contains a number, but it will also accept it when the password contains no capitals at all, or only capitals, this confuses me, and i've tried many combinations

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

that also didn't work, i have tried many code snippets from stackoverflow and people have marked it as answered, this makes me wonder, is it a problem on my part or am i doing something wrong? i am very confused right now

(len([PASSWORD])>(5) AND [PASSWORD] like '%[A-Z]%' AND [PASSWORD] like '%[0-9]%')

this should not allow the password: "wefwefew123" right? well, it does. it does not accept "wefwefew" though, so it does work with 0-9, what's wrong with the A-Z?

It's probably an dumb mistake and i will probably feel dumb after, but i can't solve it on my own, do keep in mind I'm no pro ;)

Solution

  • Try using a Case Sensitive Collation to help you out and compare password with a lower case version of it like below using your code:

    (len([PASSWORD])>(5) AND [PASSWORD] like '%[0-9]%' 
AND [PASSWORD] <> Lower([PASSWORD]) COLLATE Latin1_General_CS_AI)

    Or my own example would be like this:

    Declare @Password varchar(20) = 'Hello'
if @Password <> Lower(@Password) COLLATE Latin1_General_CS_AI
    PRINT 'Upper'
else
    PRINT 'lower'