Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"
share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
You could also weigh rows where the search expression appeared multiple times higher. –  Martin Smith Jan 18 '12 at 13:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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