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.

9 thoughts on “SQL Server: Check Whether All Characters In a String Are in Uppercase or Not

  1. Just wondering.. wouldn’t using ASCII limit your check to just strings containing only ASCII characters? Also, you might want to try a slightly more simple approach along the lines of:

    create function fnChkAllCaps1(@P_String Varchar(500))
    returns bit
    as
    begin
    return (select case when @P_String collate SQL_Latin1_General_CP1_CS_AS = upper(@P_String) then 1 else 0 end)
    end

    I believe this will work with pretty much everything, as well as being a little more clean and less costly.

    Please let me know what you think.

    Eric

  2. I also wanted to say that I found this article due to looking for information on looping through strings. So Thank you, it has been very helpful.

  3. mas aku mo nanya donk, kalo kita mau pake https, bagaimana ya caranya dan apa aja yang meski kita persiapkan, sori nih agak OOT, terima kasih ya untuk coding yang diatas… aku pake buat ngasih tau temen ku yang lagi kesulitan buat koding check number di SQL Server… salam kenal 🙂

  4. sorry i though you are an indonesian… can you help me to explain how and what do I have to prepared to use https, sorry if my question out of topics…but i really need a help for this,

    By the way thanks for the code of SQL Server, I used this code, for help my friends to build a check number SQL Server function…

    I hope i will get the answer soon, 🙂 nice to know you…

  5. Thank you for posting this but thanks to Eric J. Crew for solving the checking of the entire string verses only the last character read. I can easily turn this around and check for only lower case as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s