0

In my product table there are data/names that have car as substring or as a whole word. Now I am searching for datas that has "car" on it. I am using LIKE keyword as below and results are fine. 

product_name LIKE '%car%'

During testing i found that "cars" didn't return same results as "car" because datas didn't have 'cars' substring but car. Is there a technique where we could match closest to the keyword without maintaining/querying separate tables for words and synonyms etc?

product_name LIKE '%cars%'

Objective: If I am searching for 'cars' then i should be able to get results for cars, car, mars etc. Closest matches to given keyword from database. This is just an example. 'Keyword' is dynamic so i want closest matched results to keyword as explained in example of 'car'.

I have looked for various solutions. One solution was Levenshtein distance, that i maintain/external party for words and synonyms and apply levenshtein for closest relevant keyword and use them. This could be expensive for me. I was wondering if mysql has any native technique for this kind of matching pattern?

padam thapa
  • 1,471
  • 2
  • 20
  • 41
  • Can you elaborate a bit more? – FirstOne Oct 15 '15 at 15:18
  • @FirstOne have edited the question and mentioned what i am looking for on objective. – padam thapa Oct 15 '15 at 15:25
  • The only way to match `cars`, `car`, and `mars` would be to use `product_name LIKE '%ar%'`. That will find any string that has 'ar' with **anything** before it and **anything** after it – Lynch Oct 15 '15 at 15:31
  • @Lynch keyword is dynamic and that was just an example. There could be other keyword and i want closest match to keywords that are present in database values. – padam thapa Oct 15 '15 at 15:32
  • You wouldn't be able to do that purely in SQL, you would have to integrate some sort of search engine to find similar values. Have a look [here](http://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity). You might be able to find something useful in there – Lynch Oct 15 '15 at 15:34
  • [Hope this will help](https://stackoverflow.com/questions/34373140/find-nearest-matching-strings-and-its-substrings-using-sql-query) – Alive to die - Anant Feb 03 '21 at 04:52

0 Answers0