How to ORDER BY LIKE in MySQL?

MySQLMySQLi Database

To order by like in MySQL, use the case statement. The syntax is as follows −

SELECT *FROM yourTableName
   ORDER BY CASE
   WHEN yourColumnName like '%yourPatternValue1%' then 1
   WHEN yourColumnName like '%yourPatternValue2%' then 2
else 3
end;

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

mysql> create table OrderByLikeDemo
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (1.84 sec)

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

mysql> insert into OrderByLikeDemo values(100,'John Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByLikeDemo values(101,'Carol Taylor');
Query OK, 1 row affected (0.23 sec)

mysql> insert into OrderByLikeDemo values(102,'David Miller');
Query OK, 1 row affected (0.12 sec)

mysql> insert into OrderByLikeDemo values(103,'Mike Taylor');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByLikeDemo values(104,'Bob Miller');
Query OK, 1 row affected (0.11 sec)

mysql> insert into OrderByLikeDemo values(105,'Sam Williams');
Query OK, 1 row affected (0.16 sec)

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

mysql> select *from OrderByLikeDemo;

The following is the output −

+------+--------------+
| Id   | Name         |
+------+--------------+
| 100  | John Smith   |
| 101  | Carol Taylor |
| 102  | David Miller |
| 103  | Mike Taylor  |
| 104  | Bob Miller   |
| 105  | Sam Williams |
+------+--------------+
6 rows in set (0.00 sec)

Here is the query to get all records with ORDER BY like −

mysql> select *from OrderByLikeDemo
   -> order by case
   -> when Name like '%Taylor%' then 1
   -> when Name like '%Miller%' then 2
   -> else 3
   -> end;

The following is the output −

+------+--------------+
| Id   | Name         |
+------+--------------+
| 101  | Carol Taylor |
| 103  | Mike Taylor  |
| 102  | David Miller |
| 104  | Bob Miller   |
| 100  | John Smith   |
| 105  | Sam Williams |
+------+--------------+
6 rows in set (0.00 sec)
raja
Published on 31-Jan-2019 16:03:31
Advertisements