CODEWITHSUNDEEP
sqlsearchexpression
Nick
Nick

Reputation: 2928

Sql advanced searching

I'm trying to create a search engine for e-shop and I want make this:

If the client writes e.x galaxy white in the search bar I want to execute a sql query that matches that string with the products not only the ones with a title like galaxy white s3 but also with titles like galaxy s3 white 16 gb.

Is that possible?

Upvotes: 0

Views: 3508

Answers (2)

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

You can easily implement a search by looking at the distance between the searched keywords and the titles in the database. Levenshtein distance algorithm is a good distance algorithm.

UPDATE: For the subword string search

Levenshtein is good for mispellings. In addition to that you can split the string into words and then make a Naïve string search for your words. Then you can union the results with Levenshtein results.

In e.g., if user searches for "galaxy white s4";

You can split this string into three words:

galaxy
white
s4

Then for each word, you can make a Naïve string search in the titles such as :

SELECT * FROM Titles 
WHERE 
Titles LIKE '%galaxy%' OR 
Titles LIKE '%white%' OR
Titles LIKE '%s4%'

Up to now, this should solve your problem. But I have a few suggestions!

Michal Borek's suggestion for having synonyms is a great idea. It would improve your results. Also I would suggest using tags, for example if user searches for an attribute say bluetooth, you may want to return the devices with bluetooth support.

Upvotes: 1

Michal Borek
Michal Borek

Reputation: 4624

To be more accurate it may be needed to provide "similar phrases" (synonyms). Because e.g. you're searching for a phone on Windows Phone 8, and you may just type "WP8", Levenshtein distance won't solve that problem.

Upvotes: 1

Related Questions