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

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

Hello Emiel,

To add to Mark’s answer, the reason it doesn’t work is because of the tA-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 '%eA-Z]%'

select 1
where 'abc' COLLATE SQL_Latin1_General_CP1_CS_AS like '%eABCDEFGHIJKLMNOPQRSTUVWXYZ]%'

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.