How to Perform Case-Sensitive Searches in SQL Server's WHERE Clause?

Achieving Case-Sensitive Searches in SQL Server's WHERE Clause

Standard SQL Server queries are inherently case-insensitive when comparing strings. To perform a case-sensitive search, several techniques are available.

Approach 1: Adjusting Collation Settings

The underlying collation setting dictates the case sensitivity of string comparisons. By changing the collation to a case-sensitive option, you can enforce case-sensitive behavior within your query. Example:

<code class="language-sql">SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
AND     OrderID = @OrderID COLLATE SQL_Latin1_General_CP1_CS_AS</code>

Approach 2: Modifying Column Attributes

Alternatively, you can modify the properties of the columns participating in the search to make them case-sensitive. This is a schema-level change affecting all queries using those columns.

Approach 3: Utilizing LIKE with Collation

The LIKE operator can also be combined with COLLATE to achieve case-sensitive matching:

<code class="language-sql">SELECT *
FROM tbl_Partners
WHERE PartnerName COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'MyEx%' COLLATE SQL_Latin1_General_CP1_CS_AS</code>

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