SQL Server function to determine if all characters are capitalized

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


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.


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))
@return BIT
DECLARE @position INT

@position 1

WHILE @position <= DATALENGTH(@string)
ASCII(SUBSTRING(@string@position1)) BETWEEN 65 AND 90 
SELECT @return 0
@return 1

IF @Return <> 1
SET @position @position 1

RETURN @return


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

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 Back To Top (10430)

Why not just do something like this:

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

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 Back To Top (3639)

-- 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))
  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
    IF ASCII(SUBSTRING(@inString , @Position, 1)) BETWEEN 65 AND 90 
      SET @Position= @Position + 1
      SET @ReturnFlag = 0
  RETURN @ReturnFlag



Saturday, November 10, 2007 - 11:24:41 AM - admin Back To Top (93)


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 Back To Top (88)


DECLARE @string TABLE(test varchar(20))

INSERT INTO @string(test)

WHEN test LIKE '%[^A-Z]%' 
OR test <> UPPER(testCOLLATE SQL_Latin1_General_Cp1_CS_AS 
THEN 'NOTUpper' -- or "1" (although given function name from tip, s/b "0")
ELSE 'ISUpper'  -- or "0"
To ignore non-alpha characters (which is closer to the description), simply remove the "LIKE" test in the predicate.

get free sql tips
agree to terms