SQL Server function to determine if all characters are capitalized

Problem

Working with strings in SQL Server is not as simple as you would hope for a lot of things that you need to do with text data.  It is sometimes simpler to do a lot of these things outside of SQL Server, but if you have time and patience to write T-SQL code you could just about do anything you need to do.  One of my programmers asked if there was a way to determine if all characters in a string were capitalized. I was not able to find an existing function so I figured I’d create my own.  Take a look at this function to see if all characters are capitalized in a string.

Solution

To determine whether all of the characters are in a string or not, I created the following SQL Server function to help in this process.

ASCII values seems like a logical method of determining capitalization. ASCII values for capitalized letters are in the range of 65 – 90. Therefore I accept the string, iterate through the individual characters to determine the ASCII value. If the value falls in the appropriate range then I move onto the next character, otherwise I exit the routine.

If the entire string is capitalized, the function returns 0 (successful). Otherwise, the function returns 1 (not successful).

CREATE FUNCTION udf_AllCaps (@String VARCHAR(500))
RETURNS bit
AS
BEGIN
   DECLARE @return BIT
   DECLARE @position INT

   SET @position = 1

   WHILE @position <= DATALENGTH(@string)
   BEGIN
       IF ASCII(SUBSTRING(@string, @position, 1)) BETWEEN 65 AND 90 
           SELECT @return = 0
       ELSE
           SELECT @return = 1

       IF @Return <> 1
           SET @position = @position + 1
       ELSE
           GOTO ExitUDF
END

ExitUDF:
RETURN @return

END

Here are some sample queries using this function:

SELECT dbo.udf_AllCaps(‘MSSQLTips.com’)  — returns 1 because of “ips.com”

SELECT dbo.udf_AllCaps(‘MSSQLTIPS.COM’) — returns 1 because of “.” is not a capital letter

SELECT dbo.udf_AllCaps(‘MSSQLTIPSCOM’) — returns 0 because all characters are capitalized

 

Next Steps

  • Take this to the next level and add additional code to check for other characters and allow none letters such as “.” to still pass through as a successful value
  • Take a look at the ASCII function to see if there are other processes where you might be able to benefit from this built in SQL Server function

 

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *