SQL Server function to determine if all characters are capitalized


By:   |   Updated: 2007-11-07   |   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@position1)) 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

 



Last Updated: 2007-11-07


get scripts

next tip button



About the author
MSSQLTips author Tim Ford 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 09, 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 09, 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 07, 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(testCOLLATE 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


download





Recommended Reading

Validate Integer and Decimal Values in SQL Server

Computed Columns with Scalar Functions SQL Server Performance Issue

Auto Generate Create Table Script Based on SQL Server Query

Four ways to improve scalar function performance in SQL Server

SQL Server User Defined Function Example








get free sql tips
agree to terms


Learn more about SQL Server tools