Case insensitive Predicate in WHERE clause without Changing Collation

Question

Say I want to get all the people with the surname Smith

SELECT *
FROM   Person
WHERE  LastName = 'Smith'

The above is fine. However, as my Database Collation is CS, a LastName value like SmItH, smith or SMITH will not be included in the results above.

I could do

SELECT *
FROM   Person
WHERE  UPPER(LastName) = 'SMITH'

Which would work, however the query isn't then SARGable. However, the rub is that this will cause a table / index scan rather than a seek.

I know I can change the column or database collation but is there a way I can make the query SARGable without making any database changes?

If the operating collation is CI, `WHERE LastName = 'Smith'` will find all case combinations — MarkD, Nov 14 '17 at 16:08
Try your first query. By default sql server is not case sensitive so it should find all smith case combinations — GuidoG, Nov 14 '17 at 16:09
You should add the info that your collation is CS in your question, not in a comment. That is very very vital information — GuidoG, Nov 14 '17 at 16:10
If your collation is CS and you want a CI search, you're hosed. The closest thing to "no database changes" (as in, no backwards incompatible changes, though you'll still need to change the actual queries) is an index on a computed column `AS LastName COLLATE Latin1_General_CI_AS` (or suchlike). Obviously, this will increase storage, but TANSTAAFL. — Jeroen Mostert, Nov 14 '17 at 16:14
interesting read for you https://stackoverflow.com/questions/24473521/sql-server-like-query-not-case-sensitive — GuidoG, Nov 14 '17 at 16:15
you can try something like SELECT * FROM Person where LastName = 'Smith' COLLATE SQL_Latin1_General_CP1_CI_AS — GuidoG, Nov 14 '17 at 16:20

score 1 · Accepted Answer · answered Nov 14 '17 at 20:28

You can change the collation in your query by placing it after the where clause. You can also mix the collations used within a where clause. (The following example is pointless beyond demonstrating using two different collations.)

SELECT  *
  FROM  sys.objects AS o
  WHERE UPPER( o.name ) = o.name COLLATE SQL_Latin1_General_CP1_CS_AS
        AND o.name = o.name COLLATE SQL_Latin1_General_CP1_CI_AS

The collation test can also be used in a case statement.

SELECT  o.name,
        CASE
          WHEN UPPER( o.name ) = o.name COLLATE SQL_Latin1_General_CP1_CS_AS
            THEN 'Upper Case'
          WHEN LOWER( o.name ) = o.name COLLATE SQL_Latin1_General_CP1_CS_AS
            THEN 'Lower Case'
          ELSE 'Mixed Case'
        END
  FROM  sys.objects AS o
  WHERE o.is_ms_shipped = 0
;

Note the use of UPPER/LOWER was only to demonstrate the case sensitive nature once the collations were included.

score 0 · Answer 2 · answered Nov 14 '17 at 19:43

Maybe T-SQL pattern matching would be helpful: https://technet.microsoft.com/en-us/library/ms187489(v=sql.105).aspx

It's not pretty but this should work:

SELECT *
FROM Person
WHERE LastName LIKE '[Ss][Mm][Ii][Tt][Hh]'

I'm not 100% sure if SQL Server is able to use indexes with this sort of query.

Maybe something like this would be better for the indexes:

SELECT *
FROM Person
WHERE LastName LIKE 'S[Mm][Ii][Tt][Hh]'
OR LastName LIKE 's[Mm][Ii][Tt][Hh]'

Case insensitive Predicate in WHERE clause without Changing Collation

2 Answers2