CODEWITHSUNDEEP
sqlsql-server
SE1986
SE1986

Reputation: 2740

Case insensitive Predicate in WHERE clause without Changing Collation

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?

Upvotes: 0

Views: 640

Answers (2)

Wes H
Wes H

Reputation: 4439

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.

Upvotes: 1

Gigga
Gigga

Reputation: 577

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]'

Upvotes: 0

Related Questions