How do I perform an exact string match on a non-case sensitive field?

Question

I would like to find all the rows in a table and match on an exact case sensitive string. Unfortunately, my table has the case insensitive collation.

What is the most efficient way to perform this.

Eg.

I would like the following to return no rows:

select * from sysobject where name = 'Sysbinobjs'

For the answer assume @match is in a variable:

declare @match varchar(4000) 
set @match = 'sysbinobjs'

EDIT

Clarification, make sure trailing spaces are treated properly, I want an exact match that takes account of trailing spaces so 'Hello' will only be matched with 'Hello' and not with 'Hello '

Answer 1

Here is a snippet of code that works, its really tricky to do this in a safe and efficient way. The double match is to avoid a table scan (look at the execution plan). The varbinary casting forces a length and case match. So trailing spaces are treated properly and casing is treated properly.

Note the type being compared needs to be identical to the type in the table for the varbinary casting to work (eg. name in sysobject is an nvarchar so the @match will need to be an nvarchar).

declare @match nvarchar(4000) 
set @match = 'sysbinobjs'


select * from sysobjects
where name = @match and
cast(name as varbinary(4000)) = cast(name as varbinary(4000))

Answer 2

Actually I don't see why you have to do the escaping first, this is built into SQL server through the COLLATE statment.

select * from sysobjects
WHERE name = @match and --Get all relevant hits from the index before doing the final case sensitive test
name COLLATE Latin1_General_CS_AS = @match COLLATE Latin1_General_CS_AS

Answer 3

Should the last line of Sam's answer be:

cast(name as varbinary(4000)) = cast(@match as varbinary(4000))

?