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:
FROM MyTable
WHERE Col3 COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%foo%'

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:
SELECT Col1, Col2, Col3 COLLATE SQL_Latin1_General_CP1_CS_AS as Col3
FROM MyTable

3 responses to “Case Sensitive SQL Searches”

  1. todd sharp Avatar
    todd sharp

    You are the smartest man on earth Ben. How in the world do you know all of this stuff?

  2. Ivan Avatar

    case sensitive also for mySql and MS Access

  3. Pete Freitag Avatar
    Pete Freitag

    It’s interesting to note that PostgreSQL is case sensitive by default, and can be made case-insensitive by using the ILIKE operator

Leave a Reply