How to use COLLATE() Clause to do Case-sensitive Search in a Column In MySQL
COLLATE clause is used to perform a case-sensitive search operation in a column in MySQL. The COLLATE clause allows users to specify the collation or the set of rules for comparing strings in SQL.
Case-sensitive Search
The utf8mb4 character set in MySQL is capable of storing any Unicode character. This is an extension of the utf8 character set. It allows characters that require up to four bytes in UTF-8, whereas utf8 only allows characters that require up to three bytes.
For instance, in Users table, if user wants to find all records where the name is ‘john’ (all lowercase), not ‘John’ or ‘JOHN’. If the column uses a case-sensitive collation (like utf8_bin), the query can be written as :
Example:
SELECT * FROM Users WHERE name COLLATE utf8mb4_bin LIKE 'john';
Output:
| name |
|------|
| john |
The SQL query written will return all records from the Users table where the name in ‘john’. The COLLATE utf8mb4_bin makes the LIKE operator case-sensitive.
Explanation:
- SELECT * FROM Users: This part of the query selects all records from the Users table.
- WHERE name COLLATE utf8mb4_bin LIKE ‘j%‘: This is the condition for selecting the records. It only selects the records where the name starts with a lowercase ‘j’. The COLLATE utf8mb4_bin makes the comparison case-sensitive, and the LIKE ‘j%’ matches any name that starts with a lowercase ‘j’.
- So above query will return the records for ‘john’ ,‘joHn’ and “John’, but not , ‘JOHN’, ‘ava’, or ‘neel’.
How to do Case Sensitive and Case Insensitive Search in a Column in MySQL
LIKE Clause is used to perform case-insensitive searches in a column in MySQL and the COLLATE clause is used to perform case-sensitive searches in a column in MySQL.
Learning both these techniques is important to understand search operations in MySQL. Case sensitivity can affect search queries in MySQL so knowing when to perform a specific type of search becomes very important. This article explains how to search with the LIKE operator and COLLATE() clause with examples.