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)) |
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