How to return results if more than 60% of string is matching

Question

I have a table where product name are saved. I want to execute a select query statement where 60% or more results is matching

For example: If somebody types "verifone 500", then the product:

1) verifone vx500
2) verifone5
3) verifon00
4) verifone 50"

should be matched as they are 60% or more than same as searched product. I have tried this code

select `product_name` from product_data where `product_name` LIKE '%verifone 500%'

but not getting desired result.

Hope any body can help?

http://stackoverflow.com/questions/5322917/how-to-compute-similarity-between-two-strings-in-mysql — b.b3rn4rd, Jun 26 '14 at 06:01
You could chop the search term down to 60% so `LIKE '%verifon%'. — Ja͢ck, Jun 26 '14 at 06:05
If your db engine supports levenschtein distance, you can use that. read more: http://en.wikipedia.org/wiki/Levenshtein_distance — Limiter, Jun 26 '14 at 06:15

score 0 · Answer 1 · answered Jun 26 '14 at 06:03

0

You should go with FULLTEXT search for MyISAM table engine!

If your table engine is MyISAM then create FULLTEXT index for field product_name.

Then try query like:

SELECT `product_name`, MATCH (`product_name`) AGAINST ('verifone 500' IN NATURAL LANGUAGE MODE) AS match
FROM `product_data`
HAVING `match` > 0.5
ORDER BY `match` DESC

answered Jun 26 '14 at 06:03

Vin.AI

2,369
2
19
40

I have tried your code but its giving me error like this "Can't find FULLTEXT index matching the column list" – Anurag Jain Jun 26 '14 at 06:20
Because you don't created a FULLTEXT index to column `product_name`. – Vin.AI Jun 26 '14 at 07:00

How to return results if more than 60% of string is matching

1 Answers1