Google Style Search Suggestions with Levenshtein Edit Distance

Question

Ok guys working on search suggestions using jQuery-UI AutoComplete with results from sql-sever 2008 db. Using AdventureWorks DB Products table for testing. I want to search across 2 fields in this example. ProductNumber and Name.

I asked 2 questions earlier relating to this...here and here

and ive come up with this so far...

CREATE procedure [dbo].[procProductAutoComplete]
(
    @searchString nvarchar(100)
)
as
begin

    declare @param nvarchar(100);
    set @param = LOWER(@searchString);

WITH Results(result)
AS
(
    select TOP 10 Name as 'result'
    from Production.Product 
    where LOWER(Name) like '%' + @param + '%' or (0 <= dbo.lvn(@param, LOWER   (Name), 6))
    union
    select TOP 10 ProductNumber as 'result'
    from Production.Product
    where LOWER(ProductNumber) like '%' + @param + '%' or (0 <= dbo.lvn(@param,  LOWER(ProductNumber), 6))
)

SELECT TOP 20 * from Results

end;

My problem now is ordering of the results...I am getting the correct results but they are just ordered by the Name or product number and are not relevant to the input string...

for example I can search for product Number starting with "BZ-" and the top returned results are ProductNums starting with "A" although I do get more relevant results elsewhere in the list..

any ideas for sorting the results in terms of relevance to the search string??

EDIT:

in regards to the tql implementation of the levenschtein distance found here(linked to in previous question)...

I am wondering what would be the best way to determine the MAX value to send to the function (6 in my example above)

Would it be best to choose an arbitrary value based on what "seems" to work well for my given data set? or would it be best to adjust it dynamically based on the length of the input string...

My initial thoughs were that the value to should be inverely proportional to the length of the searchString...so as the search string grows and becomes more specific..the tolerance decreases...thoughts??

Well, as @u07ch already answered to you in a previous question, the simpler, most efficient way to do this is with FULLTEXT index. Not only the search is faster (abysmally faster), but you can also look derivative of words AND get a ranking based on relevance. — Lamak, Dec 23 '10 at 15:40

score 0 · Answer 1 · edited May 23 '17 at 12:11

The relevance is the result of dbo.lvn(). It returns the amount of operations need to transform one string into the other. So the answer is simple:

ORDER BY dbo.lvn(@param, LOWER (Name), 6)

But this won't work in combination to the LIKE as this does not return any relevance value. But the usage of LIKE is not a good idea at all. If someone is tiping "tooth" to buy "toothpaste" he would get "bluetooth" as proposal.

To make devlim faster read here: https://stackoverflow.com/a/14261807/318765

score 0 · Accepted Answer · answered Jan 06 '11 at 12:35

0

The Full Text Search feature seems to be the way go when using SQL Server

answered Jan 06 '11 at 12:35

stephen776

9,134
15
74
123

Google Style Search Suggestions with Levenshtein Edit Distance

2 Answers2