How to check in SQL if all the characters in a string are capital
Posted on 11/11/2007 9:02:28 PM
in #SQL SERVER
Hi,
Many a times we want to check if all the characters in a string are in capital letter or not? Doing this in Dot net is very simple. But what if this kind of checking neds to be done in SQL server. We all know SQL is not the best language to do these kind of checking.
Last Day I had to some similar kind of work. I created a small function to check if all the character in a string(username etc..) are in capital letter or not. Here is the function.
CREATE FUNCTION CheckIfAllCaps (@StringToCheck VARCHAR(500))
RETURNS bit
AS
BEGIN
DECLARE @return BIT
DECLARE @position INT
SET @position = 1
WHILE @position <= DATALENGTH(@StringToCheck)
BEGIN
IF ASCII(SUBSTRINGTOCHECK(@StringToCheck, @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
Thanks
Vikram
|