SQL Server function to determine if all characters are capitalized
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).
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
- 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
View all my tips
Article Last Updated: 2007-11-07