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
