How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?

by

You can start with using SOUNDEX(), this will probably do for what you need (I picture an auto-suggestion box of already-existing alternatives for what the user is typing).

The drawbacks of SOUNDEX() are:

  • its inability to differentiate longer strings. Only the first few characters are taken into account, longer strings that diverge at the end generate the same SOUNDEX value
  • the fact the the first letter must be the same or you won’t find a match easily. SQL Server has DIFFERENCE() function to tell you how much two SOUNDEX values are apart, but I think MySQL has nothing of that kind built in.
  • for MySQL, at least according to the docs, SOUNDEX is broken for unicode input

Example:

SELECT SOUNDEX('Microsoft')
SELECT SOUNDEX('Microsift')
SELECT SOUNDEX('Microsift Corporation')
SELECT SOUNDEX('Microsift Subsidary')

/* all of these return 'M262' */

For more advanced needs, I think you need to look at the Levenshtein distance (also called “edit distance”) of two strings and work with a threshold. This is the more complex (=slower) solution, but it allows for greater flexibility.

Main drawback is, that you need both strings to calculate the distance between them. With SOUNDEX you can store a pre-calculated SOUNDEX in your table and compare/sort/group/filter on that. With the Levenshtein distance, you might find that the difference between “Microsoft” and “Nzcrosoft” is only 2, but it will take a lot more time to come to that result.

In any case, an example Levenshtein distance function for MySQL can be found at codejanitor.com: Levenshtein Distance as a MySQL Stored Function (Feb. 10th, 2007).

More Related Contents:

  1. How to convert a string to date in MySQL?
  2. How to find similar results and sort by similarity?
  3. How do you extract a numerical value from a string in a MySQL query?
  4. SQL order string as number
  5. How to remove all non-alpha numeric characters from a string in MySQL?
  6. How to escape single quotes in MySQL
  7. MySQL – length() vs char_length()
  8. Capitalize first letter. MySQL
  9. Can MySQL replace multiple characters?
  10. How to fix double-encoded UTF8 characters (in an utf-8 table)
  11. MySql – Way to update portion of a string?
  12. mysql: why comparing a ‘string’ to 0 gives true?
  13. MySQL : how to remove double or more spaces from a string?
  14. Strip last two characters of a column in MySQL
  15. MySql Query Replace NULL with Empty String in Select
  16. Last index of a given substring in MySQL
  17. Hierarchical queries in MySQL
  18. MySQL, Check if a column exists in a table with SQL
  19. Can’t connect to MySQL server on ‘localhost’ (10061)
  20. Using SQL LIKE and IN together
  21. Retrieve last inserted id with Mysql
  22. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  23. How to force case sensitive table names?
  24. How to delete duplicate records in mysql database?
  25. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  26. How to get a list of months between two dates in mysql
  27. Order by FIELD in MYSQL
  28. How to Get True Size of MySQL Database?
  29. Find method to store an IP in MySQL for better performance
  30. Ruby on Rails – cannot load such file — mysql2/2.2/mysql2 (LoadError)

Leave a Comment