Syntax error How to search for exact string in MySQL?

How to search for exact string in MySQL?

You can use binary to search for exact string in MySQL. The syntax is as follows:

SELECT * FROM yourTableName WHERE BINARY yourColumnName = yourStringValue;

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

mysql> create table ExactSearch
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> UserId varchar(10),
   -> UserName varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.69 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into ExactSearch(UserId,UserName) values('USER12','John');
Query OK, 1 row affected (0.11 sec)

mysql> insert into ExactSearch(UserId,UserName) values('12USER','Carol');
Query OK, 1 row affected (0.20 sec)

mysql> insert into ExactSearch(UserId,UserName) values('USER123','Bob');
Query OK, 1 row affected (0.15 sec)

mysql> insert into ExactSearch(UserId,UserName) values('USER231','Sam');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement:

mysql> select *from ExactSearch;

The following is the output:

+----+---------+----------+
| Id | UserId  | UserName |
+----+---------+----------+
|  1 | USER12  | John     |
|  2 | 12USER  | Carol    |
|  3 | USER123 | Bob      |
|  4 | USER231 | Sam      |
+----+---------+----------+
4 rows in set (0.00 sec)

Here is the query to search for exact string in MySQL. We are searching for string “USER123”:

mysql> select *from ExactSearch where binary UserId = 'USER123';

The following is the output:

+----+---------+----------+
| Id | UserId  | UserName |
+----+---------+----------+
| 3  | USER123 | Bob      |
+----+---------+----------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements