02 March 2011

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(@myStringCOLLATE Latin1_General_CS_AI
    2) uppercase characters: @myString UPPER(@myStringCOLLATE Latin1_General_CS_AI
    3) numbersPATINDEX('%[0-9]%',@myString0

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.

3 comments:

Anonymous said...

Incredibly helpful, thanks so much.

Roman Gherman said...

I'm glad it helped you.

Anonymous said...

Man, thanks a lot.

Post a Comment

your thoughts are welcome:

Need more? Leave comments and subscribe to my blog.

.