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

RETURNS BIT

AS

BEGIN

DECLARE @V_RetValue BIT

DECLARE @V_Position INT

SET @V_Position = 1

SET @V_RetValue = 0

--Loop through all the characters

WHILE @V_Position <= DATALENGTH(@P_String)

AND @V_RetValue = 0

BEGIN

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

ELSE

SELECT @V_RetValue = 1

--Move to next character

SET @V_Position = @V_Position + 1

END

--Return the value

RETURN @V_RetValue

END

---

Sample code to test the function

---

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

GO

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

GO

---

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.