How Do I Perform Case-Insensitive Queries in SQL Server's WHERE Clause?

Querying SQL Server with Case-Insensitive "Where" Clauses

In SQL Server, string comparisons within "where" clauses are inherently case-sensitive. This means that a query like:

SELECT * FROM myTable WHERE myField = 'sOmeVal'

will only return rows where the exact case of 'sOmeVal' matches the value in the 'myField' column.

To override this default behavior and execute case-insensitive comparisons, you can specify a collation within the "where" clause. A collation defines the rules for comparing strings, including case sensitivity. To ignore case during comparison, use a collation that specifies case insensitivity, like 'SQL_Latin1_General_CP1_CI_AS':

SELECT * FROM myTable WHERE myField = 'sOmeVal' COLLATE SQL_Latin1_General_CP1_CI_AS

This query will return all rows where the value in the 'myField' column matches 'sOmeVal', regardless of the casing.

Note that the specific collation you use may vary depending on your database configuration. Refer to the Microsoft documentation for a complete list of available collations supported by SQL Server.

The above is the detailed content of How Do I Perform Case-Insensitive Queries in SQL Server's WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!