Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 15, 2005

All about the 'Case'

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM