SQL Server function to determine if all characters are capitalized

By:   |   Updated: 2007-11-07   |   Comments (7)   |   Related: > 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

 



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


Article Last Updated: 2007-11-07

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

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

 

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














get free sql tips
agree to terms