Skip to main content
Solved

Case Sensitive SQL Comparisons

  • December 3, 2020
  • 2 replies
  • 106 views
E
Rookie

Does anyone know a good method for checking case sensitive user input using the control forms?

 

I would like to use Control forms to validate whether a user has entered at least one uppercase and one lowercase character, this is for validating password requirements. 

 

By default SQL is case-insensitive. A common workaround is using: 

COLLATE SQL_Latin1_General_CP1_CS_AS 

 

I have tried to implement this but it does not seem to be working. Am I doing something wrong? Or is there another method available for implementing case sensitive comparisons? 

My implementation

 

Best answer by Mark Jongeling

Hi Emiel,

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

Both if statements should be False

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). 

declare @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 = 1
begin
    --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
     end

end

if @correct = 1
begin   
    select 'Correct'
end
else
begin
    select 'Incorrect'
end

Hope this can help!

Kind regards,
Mark Jongeling

View original
This topic has been closed for comments

Mark Jongeling
Administrator
Forum|alt.badge.img+23

Hi Emiel,

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

Both if statements should be False

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). 

declare @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 = 1
begin
    --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
     end

end

if @correct = 1
begin   
    select 'Correct'
end
else
begin
    select 'Incorrect'
end

Hope this can help!

Kind regards,
Mark Jongeling

"Wake me up before you low-code"
E
1 person likes this
  • E
    Emiel Haarhuis
V
Moderator
Forum|alt.badge.img+4

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:

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

select 1
where '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.

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings