Using “WHERE binary” in SQL?

MySQLMySQLi Database

The binary keyword can be used after WHERE clause to compare a value with exact case sensitive match.

The following is an example −

Case 1 − Case insensitive match

The query is as follows −

mysql> select 'joHN'='JOHN' as Result;

The following is the output −

+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

In the above sample output, the result is true while we know joHN and JOHN are two different words. This is not a case sensitive match.

Case 2 − If you want case sensitive match, use the binary keyword.

The query is as follows −

mysql> select binary 'joHN'='JOHN' as Result;

The following is the output −

+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

Let us now see another query −

mysql> select binary 'JOHN'='JOHN' as Result;

The following is the output −

+--------+
| Result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

NOTE − You can use binary keyword to make your column case sensitive with the help of binary keyword whenever you create a table.

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

mysql> create table binaryKeywordDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10) binary,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.88 sec)

Insert some records in the table using INSERT command. The query is as follows −

mysql> insert into binaryKeywordDemo(Name) values('bOB');
Query OK, 1 row affected (0.15 sec)
mysql> insert into binaryKeywordDemo(Name) values('bob');
Query OK, 1 row affected (0.13 sec)
mysql> insert into binaryKeywordDemo(Name) values('BOB');
Query OK, 1 row affected (0.18 sec)
mysql> insert into binaryKeywordDemo(Name) values('Bob');
Query OK, 1 row affected (0.18 sec)
mysql> insert into binaryKeywordDemo(Name) values('bOb');
Query OK, 1 row affected (0.15 sec)
mysql> insert into binaryKeywordDemo(Name) values('boB');
Query OK, 1 row affected (0.21 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from binaryKeywordDemo;

The following is the output −

+----+------+
| Id | Name |
+----+------+
|  1 | bOB  |
|  2 | bob  |
|  3 | BOB  |
|  4 | Bob  |
|  5 | bOb  |
|  6 | boB  |
+----+------+
6 rows in set (0.00 sec)

The following is the query to exact match like case sensitive −

mysql> select *from binaryKeywordDemo where Name='Bob';

Here is the output −

+----+------+
| Id | Name |
+----+------+
|  4 | Bob  |
+----+------+
1 row in set (0.00 sec)
raja
Published on 26-Feb-2019 17:43:03
Advertisements