How to run SQL script in case sensitive mode without changing the collation

[Geek-Master]

Currently our database is set to case-insensitive so when I search for the word "Math" I can get "MATH" or "Math" and even "math". Now I would like to run a script that searches for any word that is the lower case form such as "math" or "science". Since the collation is set to "I don't care" it will ignore my request of like '[a-z]%' and return the uppercase words. I can also check this by using this script below which compares the same words but in different case:

declare @var1 varchar(4)
declare @var2 varchar(4)
set @var1 = 'acct'
set @var2 = 'ACCT'

if @var1 = @var2
print 'match'
else
print 'no match'

So my question is, is there a way to temporarily set the scope of that script to a case sensative collation for that particular script and not change the server/database collation?

thanks,
GM

[Geek-Master]

I did some more digging after I posted this and came across the COLLATE keyword. At first I thought this was only used to set columns and the database to a certain collation at creation of the object, but it can also be used as a predicate during any expression such as a WHERE clause or an IF statement like in my modified example below. I've highlited the addition in red as well as setting the string values to literals. It will work either way with or without the "N"s.

declare @var1 varchar(4) 
declare @var2 varchar(4) 
SET @var1 = N'acct' 
SET @var2 = N'ACCT'

IF @var1 = @var2 COLLATE Latin1_General_CS_AS
	print 'match'
else
	print 'no match'

My resource was in the books online for SQL 2005 under the title "Collation Precedence (Transact-SQL)". Hope this helps someone else.

-GM