mysql - sql query with typos

Question

I have this company table :

TABLE company
+----+----------------+--------+--------+
| id | name           | rating | enable |
+----+----------------+--------+--------+
| 1  | Spirit company | 3.5    | 1      |
| 2  | Test           | 2      | 1      |
| 3  | Hello world    | 4      | 1      |
+----+----------------+--------+--------+

When the user make a research, this query is made (exemple with search=spirit company):

SELECT `company`.*,
    1+IF(`name` REGEXP 'sp[iíìîï]r[iíìîï]t[ -][cç][oóòôöõø]mp[aáàâäåã][nñ][yýÿ]',10,
      IF(`company`.`name` REGEXP '(sp[iíìîï]r[iíìîï]t)|([cç][oóòôöõø]mp[aáàâäåã][nñ][yýÿ])',5,0)
      ) as `ratio`
FROM `company`
WHERE `company`.`enable`=1
ORDER BY`ratio` DESC, `company`.`rating` DESC

It's works fine! Now if the user makes a typo, it is possible to give a middle result ?

Exemple: user search 'spitit company', i want to add +3 to the ratio because just one letter is different.

So you basically need a metric, which gives you the distance between two entries. That's what the Levensthein distance algorithm does - this post gives you a mysql function for it: http://stackoverflow.com/questions/13909885/how-to-add-levenshtein-function-in-mysql — neurotic-d, Jun 06 '16 at 08:52

score 1 · Answer 1 · answered Jun 06 '16 at 09:50

It's brilliant @neurotic-d

So here the new query:

SELECT `company`.*,
    1+IF(`name` REGEXP 'sp[iíìîï]r[iíìîï]t[ -][cç][oóòôöõø]mp[aáàâäåã][nñ][yýÿ]',10,
      IF(levenshtein(`company`.`name`, 'spirit company')<3,8,
      IF(`company`.`name` REGEXP '(sp[iíìîï]r[iíìîï]t)|([cç][oóòôöõø]mp[aáàâäåã][nñ][yýÿ])',5,
      IF(levenshtein(`company`.`name`, 'spirit')<LENGTH(`company`.`name`)-LENGTH('spirit')+1,3,
      IF(levenshtein(`company`.`name`, 'company')<LENGTH(`company`.`name`)-LENGTH('company')+1,3,
      0))))) as `ratio`
FROM `company`
WHERE `company`.`enable`=1
ORDER BY`ratio` DESC, `company`.`rating` DESC

Thanks very much!

mysql - sql query with typos

1 Answers1