6

I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the results ordered by the best match of asked query.

I'll try to describe that by pseudo code

select 
*
from
articles
where item_nale like '%'+@user_input +'%'
order by "BEST MATCH"
10

It isn't possible to calculate relevance with the LIKE predicate. For SQL Server (which from previous questions I believe is your platform?) you'll want to look at full-text search which supports scoring/ranking results by relevance.

18

Since all your matches have to match the LIKE pattern in order to be included, the simple thing to do is assume that shorter values for the column you're matching are "better" matches, as they're closer to the exact value of the pattern.

ORDER BY LEN(item_nale) ASC

Alternatively, you could assume that values in which the match pattern appear earlier are "better" matches.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC

Or you could combine the two; look for earliest matches first, and within those, prefer shorter values.

ORDER BY PATINDEX('%' + @user_input + '%', item_nale) ASC, LEN(item_nale) ASC

It's not as sophisticated as what full-text indexing does, and it only accounts for matching on a single column, but it gives decent enough results.

  • You could also weigh rows where the search expression appeared multiple times higher. – Martin Smith Jan 18 '12 at 13:20
3

Generate the Levenshtein function, and order by this field.

Ref: https://www.red-gate.com/simple-talk/blogs/string-comparisons-in-sql-edit-distance-and-the-levenshtein-algorithm/

If you have this function, it could be:

select 
   field1,
   field2,
   field3,
   fieldN,
   item_nale,
   MyLevenshteinFunction(item_nale,@user_input) LevenshteinDistance
from (
    select 
       field1,
       field2,
       field3,
       fieldN,
       item_nale,
       LevenshteinDistance
    from
    articles
    where item_nale like '%'+@user_input +'%'
) as result
order by LevenshteinDistance DESC
  • Welcome on the DBA SE! Note, your posts look better if all of them is on English. So everybody can read them, not only Spanish speakers. – peterh Aug 25 '18 at 3:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.