- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.