How do you force MySQL LIKE to be case sensitive?

MySQLMySQLi Database

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.

raja
Published on 11-Jan-2019 07:51:06
Advertisements