SQL Server function to determine if all characters are capitalized

By: Tim Ford | Comments (7) | Related: More > Functions User Defined UDF

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

About the author

Tim Ford is a Senior Database Administrator with MindBody.

View all my tips

Comments For This Article

Tuesday, August 26, 2014 - 11:16:17 AM - Kaushal	Back To Top (34268)
Good post for sql learner

Friday, February 17, 2012 - 11:44:40 AM - Radioman	Back To Top (16053)
what hpnepas for me is i check the box for License Terms then it goes to the set up support rules shows the box that its processing or somethin like that.. then after that NOTHING hpnepas what is goin on??

Thursday, December 9, 2010 - 9:22:43 AM - WiseOldMan	Back To Top (10431)
I guess I missed Troy's comment above because I was going to add that you can do this without a function. Troy, you beat me to it.

Thursday, December 9, 2010 - 9:19:41 AM - WiseOldMan

Why not just do something like this:

CREATE FUNCTION udf_AllCaps (@String VARCHAR(500))
RETURNS bit
AS
BEGIN
DECLARE @return bit
SELECT @return = CASE WHEN @String LIKE '%[^A-Z]%' THEN 1 ELSE 0 END
RETURN @return
END

BTW, your logic seems backwards to me. If I called a function udf_AllCaps, I would expect a 1 when it is all caps and a 0 otherwise.

Thursday, June 25, 2009 - 1:11:45 PM - ericwood8

-- My comments are general programming thoughts that you could use on your programming of SPs and UDFs.

-- 1) Minimize function calls on loop decision maker. No reason to call DATALENGTH multiple times.

-- 2) Eliminate GOTO and some extra lines by requiring good ReturnFlags to continue.

ALTER FUNCTION fnAllCaps (@inString VARCHAR(500))
RETURNS bit
AS
BEGIN
DECLARE @ReturnFlag BIT, @Position INT, @StrLength INT

SET @ReturnFlag = 1
SET @Position = 1
SET @StrLength = DATALENGTH(@inString) -- string length not going to change

WHILE ((@Position <= @StrLength) AND (@ReturnFlag = 1)) -- leave loop if bad character found
BEGIN
    IF ASCII(SUBSTRING(@inString , @Position, 1)) BETWEEN 65 AND 90
      SET @Position= @Position + 1
    ELSE
      SET @ReturnFlag = 0
END

RETURN @ReturnFlag
END

Saturday, November 10, 2007 - 11:24:41 AM - admin

Troy,

Thank you for the contribution! This is another great alternative.

If you have any other T-SQL scripts to share with the community we would love to focus a future tip or two on them.

Thank you,
The MSSQLTips.com Team

Wednesday, November 7, 2007 - 1:51:28 PM - TroyK

DECLARE @string TABLE(test varchar(20))

INSERT INTO @string(test)

SELECT 'ABCDEF' UNION ALL

SELECT 'ABCdef' UNION ALL

SELECT 'ABC.DEF'





SELECT test, 

   CASE 

       WHEN test LIKE '%[^A-Z]%' 

         OR test <> UPPER(test) COLLATE SQL_Latin1_General_Cp1_CS_AS 

   THEN 'NOTUpper' -- or "1" (although given function name from tip, s/b "0")

   ELSE 'ISUpper'  -- or "0"

   END

FROM @string

GO

To ignore non-alpha characters (which is closer to the description), simply remove the "LIKE" test in the predicate.

TroyK