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

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 '

Answers


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))

Need Your Help

Creating custom data types with constraints

haskell

I'm trying to create a custom data type. As an example

PDOStatement execute SQLSTATE invalid parameter number

php mysql sql pdo

I started using PDO for its multiple database support in PHP/MySQL but for some reason one query keeps producing an error message:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.