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.

Demo MySQL Database

Let’s create a demo MySQL table that will be used for examples in this tutorial.

Users Table:

name
John
john
ava
joHn
JOHN
neel

To create this table, write the following MySQL queries:

MySQL

CREATE TABLE Users (
   name VARCHAR(255)
);
INSERT INTO Users VALUES 
   ('John'),
   ('john'),
   ('ava'),
   ('joHn'),
   ('JOHN'),
   ('neel');

COLLATION

In MySQL, a collation determines how string comparison is done. It’s a set of rules that define how characters in a character set are compared and sorted.

By default, MySQL uses a case-insensitive collation for string comparisons. This means that when MySQL compares two strings, it considers ‘A’ and ‘a’ to be the same. For example, if a record in database is the name ‘John’, and user run a search for ‘john’, MySQL will return the record with the name ‘John’. This is because it uses a case-insensitive collation, so ‘John’ and ‘john’ are considered equal.

Example:

Let’s say we have a table Users in which there is a column name

SELECT * FROM users WHERE name = 'john';

Output:

| name  |
|-------|
| John  |
| john  |
| joHn  |
| JOHN  |

Using LIKE clause to do Case-Insensitive Search in a Column

The LIKE clause and wildcards in MySQL are powerful tools used in the WHERE clause of a SELECT, UPDATE, or DELETE statement to filter results. It allows you to search for a specified pattern within a column.

Example:

SELECT * FROM Users WHERE name LIKE 'john';

This query will return all customers whose name is “john”. Since it is a case-insensitive search, the query returns multiple rows.

| name |
|------|
| John |
| john |
| joHn |
| JOHN |

Using COLLATE() Clause to do Case-sensitive Search in a Column

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:

1. SELECT * FROM Users: This part of the query selects all records from the Users table.
2. 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’.
3. So above query will return the records for ‘john’ ,‘joHn’ and “John’, but not , ‘JOHN’, ‘ava’, or ‘neel’.

Conclusion

MySQL provides powerful tools for string comparison and pattern matching in SQL queries. The LIKE operator, used in conjunction with wildcards, allows for flexible pattern matching within a column. By default, MySQL uses a case-insensitive collation for string comparisons, treating ‘A’ and ‘a’ as the same. This is useful for most general use-cases.

However, there are situations where case-sensitive search is required. For such scenarios, MySQL allows the use of the COLLATE clause to specify a case-sensitive collation for the query. This can be particularly useful when dealing with data that is case-sensitive, such as passwords or unique codes.

Remember, the choice of collation (case-sensitive or case-insensitive) depends on the specific requirements of database and the nature of the data. Always choose the one that best fits your needs.