If we are searching a word (string) in the mysql table, usually we will use the below method:
[sql]
SELECT * FROM user WHERE name LIKE ‘%searchstring%’ ORDER BY name ASC;
[/sql]
If you search like this, It will order the result set based on the ASC of name. It will not order the result set based on the string found at the beginning, middle and last position.
For Example:
If you are searching for name as “KRISHANA” in user table, from the following records:
1) ANANDA KRISHNAN
2) KRISHANA MORTHI
3) GEETHA RAMA KRISHANAN
4) KRISHANA
It will display the result set as below:
1) ANANDA KRISHNAN
2) GEETHA RAMA KRISHANAN
3) KRISHANA
4) KRISHANA MORTHI
But best matching record should always display on top of the resultset. To display the resultset based on matching string at position of beginning, middle and last. Follow the below steps:
[sql]
SELECT name
FROM users
WHERE name LIKE ‘%KRISHNA%’
ORDER BY
CASE
WHEN name LIKE ‘KRISHNA%’ THEN 1
WHEN name LIKE ‘%KRISHNA%’ THEN 2
ELSE 3
END
[/sql]
Output:
As the result of the Query, The resultset will be like this:
1) KRISHANA
2) KRISHANA MORTHI
3) ANANDA KRISHNAN
4) GEETHA RAMA KRISHANAN
It Will automatically sort the record based on best matching string. Thanks for reading this post, If you have any ideas or comments please share it.