This SQL thing just keeps on getting better. After looking at the link I provided in my last post, I though that I could do something like that but which uses multiple tables and/or search words. Not particularly hard, I know, but very useful when it comes to databases that may or may not have full-text searching enabled.
Here’s the code:
select title, keywords
, (
ISNULL((len(title) - len(replace(title,'search1',''))) / len('search1'),0) +
ISNULL((len(keywords) - len(replace(keywords,'search1',''))) / len('search1'),0) +
ISNULL((len(title) - len(replace(title,'search2',''))) / len('search2'),0) +
ISNULL((len(keywords) - len(replace(keywords,'search2',''))) / len('search2'),0) +
0
) as occ
from table_name
Where
title like '%search1%' or keywords like '%search1%'
Or
title like '%search2%' or keywords like '%search2%'
order by occ desc
As you can see, it is pretty easy to use some simple string concatenation to loop a number of search words, and even loop several search fields as well. I will probably end up using this method to provide much better quality search results in almost every database search script I write. However there may well be a better way to do this, let me know if you use something different.