/*
To test string equality including case when the DB is
not case sensitive you must
include collation as part of the test as follows:
*/
declare @mystring1 varchar(10)
declare @mystring2 varchar(10)
set @mystring1 ='abc'
set @mystring2 ='ABC'
-- by default
if @mystring1 = @mystring2
print 'match'
else
print 'not a match'
-- by adding a case sensitive colation whe can check case
if @mystring1 = @mystring2 COLLATE Latin1_General_CS_AS
print 'match'
else
print 'not a match'
-- now make them equal in case and test again
set @mystring2 ='abc'
if @mystring1 = @mystring2 COLLATE Latin1_General_CS_AS
print 'match'
else
print 'not a match'
/*Here is a sample use */
use Northwind
go
select * from customers
where companyname='alfreds futterkiste' -- works
select * from customers
where companyname='alfreds futterkiste' COLLATE Latin1_General_CS_AS --fails
select * from customers
where companyname='Alfreds Futterkiste' COLLATE Latin1_General_CS_AS --works