How can I do a case-sensitive comparison on a SQL Server installed with a case-insensitive sort-order?

A. You can do this by converting the values to binary.

Assuming that you want to check a 4 character field to see if it has 'teST' in it (and you don't want to return values of 'test', 'TEST' etc.)

select * from <tbl>
where convert(varbinary(4), <col>) = convert (varbinary(4), 'teST')

To compare between tables

select * from <tbl1>, <tbl2>
where <tbl1.col> = <tbl2.col>
and convert(varbinary(4), <tbl1.col>) = convert (varbinary(4), <tbl2.col>)

The reason for having the two comparisons here is so that any indices will still be used. They won't be used just with a comparison that uses convert.


Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish