SQL Server function to determine if all characters in a string are capitalized
June 19th 2008 09:58
SQL Server function to determine if all characters in a string are capitalized
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
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
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
| 21 |
| Vote |
Add Comments
