=?Utf-8?B?RGFu?=
3/31/2007 9:38:00 PM
I need to create a better search on an ecommerce web site. Using the
Northwind database as an example, here is what I need to be able to do.
In Northwind.Categories.CategoryName, there is a value of "Dairy Products"
In Northwind.Products.ProductNames there are 2 products with values of
"Queso Cabrales" and "Queso Manchego La Pastora" that are related to the
"Dairy Products" category.
The following query works for the given search phrase:
SELECT p.productid, p.productname, c.categoryname
FROM products p
JOIN [Categories] c ON p.[CategoryID] = c.[CategoryID]
WHERE ProductName + ' ' + c.CategoryName
LIKE '%Queso Cabrales Dairy Products%'
The problem is that it obviously won't work for more flexible search phrases
such as "Queso Dairy", "Queso Products", or "Dairy Queso".
How can I make my search more flexible, intelligent, and accurate?
And to take it to the next level, how could I go about handling
miss-spellings, or things like plural/singular case.