MySQL order by how much it's like a string

Question

I have the following MySQL query:

SELECT * FROM users WHERE name LIKE '%jack%'

I want it to order by how much it's like jack so

1. jack
2. jacker
3. majack

I can also use PHP.

Answer 1

I think you can accomplish what you want by using full text search function in mysql. Your query will be like this:

SELECT name, MATCH (name) AGAINST ('jack') as score 
FROM users ORDER BY score DESC;

There are some conditions you need to take into consideration when using full search text:

• The mysql engine should support this functions. I think InnoDB 5.6 and MYISAM 5.5 support that.
• You have to add a FULLTEXT index in your table definition.

You can see a working demo here: http://sqlfiddle.com/#!9/72bf5/1

More info about full search text in MySQL here: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Also, here is a working example I wrote on PHP using similar_text and sorting array functions: http://ideone.com/UQpBFk

Hope it helps!

Answer 2

I don't think you can do this kind of thing with plain SQL. The first thing you need to do is define what it means for two strings to be similar, for which there are various metrics. You should probably pick one of those and write a stored procedure to sift through the data.

Answer 3

As every one mentioned, you can't achieve this using normal way other than using full text search but if you know all the different pattern before hand then you can use FIELD function to achieve something which resemble a approx result like

SELECT * FROM users WHERE name LIKE '%jack%'
ORDER BY FIELD (name,'jack','jacker','majack') 

Answer 4

Try something like this:

$query = mysql_query("SELECT * FROM users WHERE name LIKE '%jack%' ORDER BY name ASC ");

while($fetchdata=mysql_fetch_array($query))
{
echo $fetchdata["name"] ; 
}