Comparing Uppercase and Lowercase in SQL Server

Occasionally it is necessary to really tell whether or not “THIS” is equal to “this” or “This”, including the cases.  For me, this came up when trying to assess whether a name in the database was already in mixed case or not.  If it was upper, I needed to convert it to mixed case, but if it was already in mixed case, I wanted to leave it alone.

If you just do a straight compare, case is not taken into account.  So, we need to somehow compare the actual character values of these.  It turns out that if we cast them to varbinary values, the uppercase letters do get different values than the lowercase letters that we can compare and determine the difference.

So, in my case, I decided that if the first character was in lower case or the second character was in upper case, I was probably dealing with a name that was in either all caps or all lowercase.  This is how I tested for that:

SELECT *
FROM Person
WHERE CAST(SUBSTRING(LastName, 1,1) as varbinary) = CAST(SUBSTRING(LOWER(LastName), 1,1) as varbinary)
	OR CAST(SUBSTRING(LastName, 2,1) as varbinary) = CAST(SUBSTRING(UPPER(LastName), 2,1) as varbinary)

Basically, it checks to see if the varbinary representation of the first character is equal to the varbinary representation of the uppercase value of that character OR if the varbinary representation of the second character is equal to the varbinary representation of the lowercase of that character.

This allowed me to focus in on converting only the names that were not already in mixed case/camel case.

This also works well in a function:

CREATE FUNCTION [dbo].[IsUpperCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsUpperCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(UPPER(@Char) AS VARBINARY))
		SELECT @IsUpperCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsUpperCase = CAST(0 AS BIT)

	RETURN @IsUpperCase
END

CREATE FUNCTION [dbo].[IsLowerCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsLowerCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(LOWER(@Char) AS VARBINARY))
		SELECT @IsLowerCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsLowerCase = CAST(0 AS BIT)

	RETURN @IsLowerCase
END