Case Sensitive SQL Searches

SQL searches are usually case-insensitive, because most databases are set up by default for case-insensitive searching. Case sensitivity is defined by collation sequences, rules which define how strings are compared taking into account the specifics of individual languages (case, special characters, and more). Collation sequences can be defined at the database server level, at the specific database level, at the table level, and also at the individual column level. Collation sequences can also be overridden, so if a collation sequence is defined for a database or table, a specific column can have an alternate collation sequence.

Why do I bring this up? An app I am working on needs to use LIKE to perform wildcard searches against a table column, but those searches must be case-sensitive (so that %foo% will not match FooBar).

The simple solution would have been to modify the table so that the column used in the search would use a case-insensitive collation sequence. But that would have messed up other searches that need to remain case-insensitive.

So what to do? Here are a couple of solutions.

Collation sequences may be defined inline, right in the WHERE clause, as seen here:

In this example, the default collation sequence was SQL_Latin1_General_CP1_CI_AS (the CI indicates case-insensitive), but in the above WHERE clause an inline COLLATE statement is specified so that collation sequence SQL_Latin1_General_CP1_CS_AS (the CS indicates case-sensitive) is used in this search.

Another solution, better suited for situations where both case-sensitive and case-insensitive searches are frequently needed, is to define the table with one sequence (whichever sequence will be used more frequently), and a VIEW based on that table with another. Here is an example, setting an explicit collating sequence for one column: