Sql Server Ignore Case in a Where Expression

Quick COLLATE crash course

COLLATE can be defined at different levels; within a query, for a column, or for the entire database. So, if the default case sensitivity of SQL Server or your database's collation doesn't suit your current needs, use COLLATE within your WHERE clause:

-- This is like a secret handshake for SQL Server
SELECT * FROM Employees WHERE FirstName COLLATE Latin1_General_CI_AS = 'john';


Regardless of how 'John' is spelled (maybe 'JOHN' or even 'joHn'), SQL Server will fetch the correct rows.

Know your collation

The collation of a database or column can override the default collation of SQL Server, which is usually case-insensitive. If you need to identify a column's collation, use this handy query:

-- Don't play hide and seek with collation
SELECT name, collation_name FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');


Common traps you might fall into

When dealing with case-insensitivity in your SQL Server queries:

• Remember VARBINARY isn't your friend here, it's meant for binary data.
• Don't overutilize UPPER(), consider using collation when appropriate.
• Don't assume LIKE will always be case-insensitive without checking the collation.

Pattern matching and collation

LIKE operator behaves according to the collation. For example,

-- But does 'laptop' LIKE 'LAPTOP'? 
SELECT * FROM Products WHERE ProductName LIKE 'laptop';


The result of this query can be 'Laptop', 'LAPTOP', or only 'laptop', it's all about the ProductName column's collation or the database default collation.

Exploring COLLATIONPROPERTY

For some deeper insights into your server's default collation, COLLATIONPROPERTY might come handy:

-- I need to check collation's depth: metrics, stylistic approaches, and more
SELECT COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle');


Case where COLLATE outperforms functions

When dealing with uncertain user inputs or collations, explicitly enforcing case-insensitivity can be a lifesaver:

-- USERNAME or username? They are all same to me!
SELECT * FROM Customers WHERE LastName COLLATE Latin1_General_CI_AS = 'smith';


Knocking out compatibility issues

If you've got an application that is supposed to run on different SQL Server environments, the COLLATE clause helps maintain consistent results:

-- PartNumber is afraid of case? Not anymore!
SELECT * FROM Inventory WHERE PartNumber COLLATE Latin1_General_CI_AS = 'abc123';
