How to Enforce Like Case Sensitive in MySQL

The keywords in any programming language like C, C++, Java, and MySQL are useful; they are called reserved words. These reserved words have their meaning and functioning.

When these keywords get used in a statement, they act according to their pre-defined behavior.

Select, Delete, and Where are examples of reserved keywords. These words show and act accordingly in a statement as per the protocol decided before the implementation.

The name of tables and variables can never be similar to the keywords as they are already reserved. And no one can programmatically use these keywords in programming practices.

Enforce the Like Keyword to Be Case Sensitive in MySQL

The like keyword in MySQL gets used along with the where keyword. Its function is to match a particular pattern.

These patterns get identified using the wildcard character or wildcards.

In a programming language, a wildcard character is a symbol that holds its meaning and gets used in pattern identification. There are two types of wildcards used in MySQL.

1. Percent sign - The % indicates the selection of zero, one, or multiple characters when this gets used with a like keyword.
2. The underscore sign - The _ sign symbolizes the single character’s presence in any word.

These symbols can be used to make combinations and combined within the like clause.

The syntax for the LIKE keyword:

SELECT * FROM table_name WHERE column_name LIKE 'ABC_';
SELECT * FROM table_name WHERE column_name LIKE 'T_ST%';

The above syntax uses the like keyword and the wildcard characters to search out some defined set of values from the tables.

The property of the like operation is that it ignores the case of the word and then tries to find the matches.

A way to achieve case-sensitivity comparison with like:

Use the LIKE BINARY keyword if a user needs to compare an exact case-sensitive match in the where clause.

Query to execute the MySQL statement:

 select * from stu;
+------+---------+------------+
| id   | Name    | DOB        |
+------+---------+------------+
| 111  | Amit    | 1970-01-08 |
| 112  | Shushma | 0000-00-00 |
| 113  | Rami    | 2020-09-08 |
| 114  | Sam     | 2020-09-08 |
| 115  | tonny   | 2020-09-08 |
+------+---------+------------+

Refer above table to understand the below two queries with the like binary keyword.

Select * from stu where name LIKE BINARY 's%';
Select * from stu where name LIKE BINARY '%mi_';

The above query lists all the occurrences of the column name, which has characters mi in between them. The BINARY keyword acts as a case-sensitive match finding in the query.

The % and _ special characters hold special meanings while finding the match with all the results set in the stu table.

The first query shows that the select query results in all the rows where the name column starts with s in lowercase. Hence the first query results in no output.

Similarly, when the query is tried again with %mi_ characters, only one result is shown, which is shown in the below screenshot.

The actual run screenshot of the above queries in MySQL is below.