CODEWITHSUNDEEP
mysql
Yuri
Yuri

Reputation: 3284

Get best match in MySQL query

I want to get results from MySQL query, sorted by best match. This means that when I a table like this:

+--------+
| Field  |
+--------+
| X01234 |
| 001234 |
| 01234  |
| XT1234 |
+--------+

and want to search for $str='012', the desired output should be like

01234
001234
X01234

To get this, the only way I found is using this UNION query:

SELECT * FROM my_table WHERE field LIKE '$str%'
UNION
SELECT * FROM my_table WHERE field LIKE '%$str%'

Is there a better way of selecting best match for searched string?

Upvotes: 0

Views: 147

Answers (2)

R.F.
R.F.

Reputation: 389

SELECT * FROM my_table WHERE field LIKE '$str%' ORDER BY my_table

Upvotes: 0

Sergey Menshov
Sergey Menshov

Reputation: 3906

Maybe it's what you want

SELECT *
FROM my_table
WHERE field LIKE '%$str%'
ORDER BY INSTR(field,'$str'),field

You also can use INSTR instead LIKE

SELECT *
FROM my_table
WHERE INSTR(field,'$str')>0
ORDER BY INSTR(field,'$str'),field

SQL Fiddle - http://www.sqlfiddle.com/#!9/d3b410/1

Upvotes: 2

Related Questions