How do you force MySQL LIKE to be case sensitive?

To force MySQL LIKE to be case sensitive with the help of LIKE BINARY, the following is the syntax −

select yourColumnName like binary 'anyStringValue' from yourTableName;

To understand the above concept, let us create a table. The following is the query to create a table −

mysql> create table LikeBinaryDemo
   −> (
   −> Name varchar(200)
   −> );
Query OK, 0 rows affected (0.58 sec)

Now you can insert records with small letters to force the MySQL LIKE to be case sensitive −

mysql> insert into LikeBinaryDemo values('john');
Query OK, 1 row affected (0.12 sec)

Display the records in the table. The query is as follows −

mysql> select *from LikeBinaryDemo;

The following is the output −

+------+
| Name |
+------+
| john |
+------+
1 row in set (0.00 sec)

Case 1 − Using BINARY

Now you can use LIKE BINARY to force the MySQL Like to be case sensitive.

LIKE BINARY. In this case, we will get value 0 when we compare ‘john’ with ‘JOHN’. The query is as follows −

mysql> select Name like binary 'JOHN' from LikeBinaryDemo;

The following is the output −

+-------------------------+
| Name like binary 'JOHN' |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

Case 2 − Without using BINARY

Now let us see what will happen when we are not using BINARY. The query is as follows −

mysql> select Name like 'JOHN' from LikeBinaryDemo;

The following is the output −

+------------------+
| Name like 'JOHN' |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)

You can use LIKE BINARY to force MySQL to be case sensitive.