MySql Near Search

Question

Example, I want to search for "John Smith" in "My name is John Joe Smith and I live in America". But with a limit of 5 characters between "John" and "Smith".

How could I get this with MySql (php)?

I am building a search engine for my database, I am already using MATCH AGAINST, but I also want to offer a different kind of search.

thank you, Andy

Answer 1

But with a limit of 5 characters between "John" and "Smith"

MySQL full text functions don't offer such flexibility as far as I know. You can use like or regular expressions:

where col regexp 'John.{0,5}Smith'

If you do the full text search in a subquery, then this might actually have reasonable performance.

The delightful like expression is:

where col like '%John%Smith%' and
      col not like '%John______Smith%'

There are six _s.

Answer 2

You may want to explore

SOUNDEX()

, this will help you to achieve what you need, e.g. auto suggest functionality. strings. but it comes with few drawbacks as only the first few characters are taken into account, first letter must be the same or you won't find a match easily.

For more advanced needs, I think you need to look at the

Levenshtein distance aka "edit distance" of two strings and work with a threshold

. This is the more complex yet slower solution, but it comes with better flexibility

You might want refer example tutorial here

https://lucidar.me/en/web-dev/levenshtein-distance-in-mysql/#:~:text=Informally%2C%20the%20Levenshtein%20distance%20between,not%20match%20exactly%20the%20fields.