Syntax error Sort search results based on substring position in MySQL

Sort search results based on substring position in MySQL

To sort search results based on substring position, use ORDER BY LOCATE(). Let us first create a table −

mysql> create table DemoTable1838
     (
     Subject varchar(100)
     );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1838 values('MongoDB MySQL');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('MySQL Java');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1838 values('JavaWithMySQL');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1838;

This will produce the following output −

+---------------+
| Subject       |
+---------------+
| MongoDB MySQL |
| MySQL Java    |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)

Here is the query to sort search results based on substring position:

mysql> select * from DemoTable1838
     where Subject LIKE '%MySQL%'
     ORDER BY LOCATE('MySQL', Subject);

This will produce the following output −

+---------------+
| Subject       |
+---------------+
| MySQL Java    |
| MongoDB MySQL |
| JavaWithMySQL |
+---------------+
3 rows in set (0.00 sec)
Updated on: 2019-12-24T07:55:43+05:30

223 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements