How Can I Perform Case-Sensitive String Comparisons in SQL?

Achieving Case-Sensitive String Comparisons in SQL

Standard SQL string comparisons often ignore case differences. A query like SELECT * FROM table WHERE column = 'value' will find matches regardless of capitalization.

Enforcing Case Sensitivity

To perform a case-sensitive comparison, utilize the COLLATE clause. This clause specifies the collation—the set of rules governing string sorting and comparison. Selecting a case-sensitive collation ensures accurate case-sensitive matching.

Illustrative Example

Imagine a table with a string attribute:

<code>| attribute |
|---|---|
| abc |
| ABC |
| aBc |</code>

A typical query:

SELECT * FROM table WHERE attribute = 'ABC';

...would return all three rows due to case-insensitive matching.

To enforce case sensitivity, use COLLATE:

SELECT * FROM table WHERE attribute = 'ABC' COLLATE Latin1_General_CS_AS;

Latin1_General_CS_AS is a case-sensitive collation. This revised query will only return the row where attribute is precisely 'ABC'.

The above is the detailed content of How Can I Perform Case-Sensitive String Comparisons in SQL?. For more information, please follow other related articles on the PHP Chinese website!