SQL - How to check if a string has uppercase / lowercase characters

Hello,

When you need to compare two strings in SQL to check if this string contains uppercase or lowercase values it is always a problem, because in sql "ExTremeDev.blogSpot.com23" is equal to "extremedev.blogspot.com23".

I also needed to do such thing, in order to make some rules for a password, so that it will contain numbers, uppercase characters and lowercase characters.

Here I will provide an example like that:

DECLARE @myString NVARCHAR(200) -- The length of the NVARCHAR is mandatory, otherwise it is not clear why but it is not working
SET @myString = 'ExTremeDev.blogSpot.com23'

IF @myString = LOWER(@myString) COLLATE Latin1_General_CS_AI
BEGIN
  PRINT '- The string must contain at least one uppercase character.'
END

IF @myString = UPPER(@myString) COLLATE Latin1_General_CS_AI
BEGIN
  PRINT '- The string must contain at least one lowercase character.'
END

IF PATINDEX('%[0-9]%',@myString) = 0
BEGIN
  PRINT '- The string must contain at least one numeric character.'
END



So to check if a string contains:
    1) lowercase characters: @myString = LOWER(@myString) COLLATE Latin1_General_CS_AI
    2) uppercase characters: @myString = UPPER(@myString) COLLATE Latin1_General_CS_AI
    3) numbers: PATINDEX('%[0-9]%',@myString) = 0

The COLLATE here is doing all the Job, so do not remove it :)

About PATINDEX you can always find some information on Microsoft: http://msdn.microsoft.com/en-us/library/ms188395.aspx

I hope this will help others as it took some time for me to find this solution.