Member Avatar for 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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.