Case Sensitive SQL Comparisons

Hi Emiel,

After checking myself, I notice the collation you use does not work for this purpose. I tried this:

For another purpose, cleaning table names, I have used the ASCII() function. It could also work for this. I rebuilded it a little for you. You can them make the changes needed to return the correct error(s). 

1declare @password varchar(256) = 'password'select @password  = trim(@password)declare @length      int          = len(@password)      , @counter     int          = 1      , @correct     int          = 1      , @ascii_code  int--Go through the password, one by one character while @counter <= @length   and @correct = 1begin    --Make a ASCII code of the string        select @ascii_code = ascii(substring(@password, @counter, 1))    --What is allowed    if @ascii_code between 48 and 57  or   --Numbers       @ascii_code between 65 and 90  or   --Uppercase letters       @ascii_code between 97 and 122 or   --Lowercase letters       @ascii_code = 95                    --Underscore     begin        --Up the counter        select @counter = @counter + 1     end     else     begin        select @correct = 0 --Password incorrect     endendif @correct = 1begin       select 'Correct'endelsebegin    select 'Incorrect'end

Hope this can help!

Kind regards,
Mark Jongeling

Hello Emiel,

To add to Mark’s answer, the reason it doesn’t work is because of the [A-Z] range in your LIKE comparison. These ranges appear to always be case-insensitive, but it does work if you write out the letters of the range.

Try out the following two queries and see how their results differ:

1select 1where 'abc' COLLATE SQL_Latin1_General_CP1_CS_AS like '%[A-Z]%'select 1where 'abc' COLLATE SQL_Latin1_General_CP1_CS_AS like '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

It's important to note that the _AS part of this collation indicates that it is accent-sensitive. This means that é will not match e and therefore a password like ÀàààÉééé will not match either of your LIKE-comparisons, causing the password to be rejected. You might want to consider the Latin1_General_CS_AI collation instead.

Hope this helps.