Olga Real Olga Real - 2 months ago 9
MySQL Question

Sort search results based on sub string position

id | col1 | col2 |
_________________________
1 | Aceh | Denponda
2 | Aceh | Bonda Aceh
3 | Sumatera | Asahan
4 | Sumatera | Ondanar


And I use this query

SELECT * FROM table WHERe col2 LIKE '%onda%'


Now what happens here is it shows result like this

id | col1 | col2 |
_________________________
1 | Aceh | Denponda
2 | Aceh | Bonda Aceh
4 | Sumatera | Ondanar


But I want is the row with id 4 takes the first place as its nearest on the similarity of the WHERE clause which is
onda


This is my desired result

id | col1 | col2 |
_________________________
4 | Sumatera | Ondanar
2 | Aceh | Bonda Aceh
1 | Aceh | Denponda


Basically what I want here is to sort the result accordingly
to the nearest similarity of the WHERE clause which is
onda
base on the value of the col2 from left to right

logically like this


  • Ondanar

  • Bonda Aceh

  • Denponda



NOTE: the
id
column can't be use for sorting.

Answer

You can order by LOCATE

SELECT 
* 
FROM table 
WHERE col2 LIKE '%onda%'
ORDER BY LOCATE('onda', col2)

Note:

 LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

Alternatively you can use INSTR function