Wednesday, November 28, 2007

SQL Server: Check Whether All Characters In a String Are in Uppercase or Not

When I was developing a small application, I had to write a Microsoft SQL Server script to check whether all the characters in a given string are uppercase alphabets or not.

Here is the Microsoft SQL Server function code which performs the check. This code is compatible with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.


 * Purpose: Check whether all characters

 *         in a string are capital alphabets or or not

 * Parameter: input string

 * Output: 0 - on success; 1 on failure


CREATE FUNCTION fnChkAllCaps(@P_String VARCHAR(500))








SET @V_Position = 1

SET @V_RetValue = 0   


--Loop through all the characters

WHILE @V_Position <= DATALENGTH(@P_String)

           AND @V_RetValue = 0



     --Check if ascii value of the character is between 65 & 90

     --Note: Ascii value of A is 65 and Z is 90

     IF ASCII(SUBSTRING(@P_String, @V_Position, 1))

            BETWEEN 65 AND 90

        SELECT @V_RetValue = 0


       SELECT @V_RetValue = 1      

   --Move to next character       

   SET @V_Position = @V_Position + 1



--Return the value

RETURN @V_RetValue



Sample code to test the function

SELECT dbo.fnChkAllCaps('TECHTHOUGHTS') -- Returns 0


SELECT dbo.fnChkAllCaps('TechThoughts') -- Returns 1


The above function iterates through all the characters of a given input string and checks whether ASCII value of the characters is between 65 and 90 to verify it is an uppercase alphabet or not.

You might be wondering why is ASCII values check is between 65 and 90. It is because the ASCII value of uppercase A is 65 and uppercase Z is 90.

Soon I'll rewrite the same code using SQL Server 2005 CLR functions and post it. I believe Microsoft SQL Server 2005 CLR functions perform this check very efficiently.

1 Comment:

Anonymous said...

Why not use the condition

(x = UPPER(x))?

Seems more efficient and requires no user-created function (since it can be used with a case statement to return a bit if needed). Generally, if I need to make sure something is in upper case, I just wrap it in UPPER without checking the original case. If I need to perform other logic based on whether the original value case, the condition I wrote above does the trick.

I'm just sayin'