All about the 'Case'

March 15, 2005

When installing SQL Server, regardless of versions and editions, SQL Server database administrators tend to choose the default collation and sort-order, which is SQL_Latin1_General_CP1_CI_AS. Though case insensitiveness makes the life of the database developers and database administrator's easy, there are situations where case sensitivity should be enforced just as password checking is enforced.

In this article, I would like to discuss different methods for achieving case sensitivity in a case insensitive database/server.

Let us assume that we are going to compare the words 'password' and 'PassWord'.

SQL Server with SQL_Latin1_General_CP1_CI_AS collation interprets both 'password' and 'PassWord' as the same word.

Example

Execute the statement below to see the case insensitive behavior

set quoted_identifier off
Declare @Word1 varchar (100)
Declare @Word2 varchar (100)
set @Word1 = 'password'
set @Word2 = 'PassWord'
if @Word1 = @Word2
begin
Print 'Both "' + @Word1 + '" 
	and "' + @Word2+'" are same.'
end
else
begin
Print '"' + @Word1 + '" 
	and "' + @Word2+'" are not same.'
end

Result

Both "password" and "PassWord" are same.

Method 1

Using the COLLATE clause we can compare words with different cases, like lower case, upper case or mixed cases.

Example

set quoted_identifier off
Declare @Word1 varchar (100)
Declare @Word2 varchar (100)
set @Word1 = 'password'
set @Word2 = 'PassWord'
if (@Word1 collate Latin1_General_CS_AI_KS_WS = @Word2 collate Latin1_General_CS_AI_KS_WS )
begin
Print 'Both "' + @Word1 + '" and "' + @Word2+'" are same.'
end
else
begin
Print '"' + @Word1 + '" and "' + @Word2+'" are not same.'
End

Result

"password" and "PassWord" are not same.

Method 2

Using the Binary_Checksum function, we can compare words with lower case, upper case or mixed cases.

Example

set quoted_identifier off
Declare @Word1 varchar (100)
Declare @Word2 varchar (100)
set @Word1 = 'password'
set @Word2 = 'PassWord'
if (BINARY_CheckSum(@Word1)= BINARY_CheckSum(@Word2))
begin
Print 'Both "' + @Word1 + '" and "' + @Word2+'" are same.'
end
else
begin
Print '"' + @Word1 + '" and "' + @Word2+'" are not same.'
Print 'Binary Checksum of "' + @Word1 + '" ='+ convert(varchar(100),
	binary_checksum(@Word1))+' and "' + @Word2+'" ='+convert(varchar(100),
	binary_checksum(@Word2))
end

Result

"password" and "PassWord" are not same. The Binary checksum of "password" =1715738691 and "PassWord" =1715869761

Method 3

Using the Varbinary data type, we can compare words with lower case, upper case or mixed cases.

set quoted_identifier off
Declare @Word1 varchar (100)
Declare @Word2 varchar (100)
set @Word1 = 'password'
set @Word2 = 'PassWord'
if (convert(varbinary,@Word1)= convert(varbinary,@Word2))
begin
Print 'Both "' + @Word1 + '" and "' + @Word2+'" are same.'
end
else
begin
Print '"' + @Word1 + '" and "' + @Word2+'" are not same.'
Print 'VarBinary value of "' + @Word1 + '" ='
print convert(varbinary(100),@Word1)
print ' and "' + @Word2+'" ='
print convert(varbinary(100),@Word2)
end

Result

"password" and "PassWord" are not same. The VarBinary value of "password" =0x70617373776F7264 and "PassWord" = 0x50617373576F7264

Method 4

Using the ASCII function, we can compare words with different cases.

set quoted_identifier off
Declare @Word1 varchar (100)
Declare @Word2 varchar (100)
declare @asciiword1 varchar(300)
declare @asciiword2 varchar(300)
declare @word1count int
declare @word2count int
set @Word1 = 'password'
set @Word2 = 'PassWord'
set @asciiword1 = ''
set @asciiword2 = ''
set @word1count = 1
set @word2count = 1 

while @word1count <=datalength (@Word1)
begin
--select right('000'+convert(varchar(3),ascii(substring (@Word1,@word1count,1))),3)
set @asciiword1 = @asciiword1 + (right('000' + convert(varchar (3), 
	ascii (substring (@Word1, @word1count,1))),3))
set @word1count = @word1count+1
end
while @word2count <=datalength(@Word2)
begin
--select right('000'+convert(varchar(3),ascii(substring (@Word1,@word1count,1))),3)
set @asciiword2 = @asciiword2 + (right('000' + convert(varchar (3), 
	ascii (substring (@Word2, @word2count,2))),3))
set @word2count=@word2count+1
end

--print @asciiword1 
--print @asciiword2
 
if ( @asciiword1 = @asciiword2 )
begin
Print 'Both "' + @Word1 + '" and "' + @Word2+'" are same.'
end
else
begin
Print '"' + @Word1 + '" and "' + @Word2+'" are not same.'
Print 'ASCII value of "' + @Word1 + '" ='+ @asciiword1+' and "' + @Word2+'" ='+@asciiword2
end

Result

"password" and "PassWord" are not same. The ASCII value of "password" =112097115115119111114100 and "PassWord" =080097115115087111114100.

Conclusion

This article has discussed the various methods of comparing two words that are the same, with the exception that they are different cases, i.e. lower case, upper case or mixed cases.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers