Good SQL search tool?

Question

FreeTextTable is really great for searching, as it actually returns a relevancy score for each item it finds.

The problem is, it doesn't support the logical operator AND, so if I have 10 items with the word 'ice' in it, but not 'cream', and vice versa, then 20 results will be returned, when in this scenario 0 should've been returned.

Are there any alternative tools to search a SQL Server database? Or should I just write my own code to provide 'AND' functionality (I.E. doing two seperate searches in the scenario 'Ice'Cream' (splitting each search by spaces))

Answer 1

You can try SQL Search from RedGate.

It is a free tool (though not open source) - I have used it before and it is very powerful.

Answer 2

Since you have full text search enabled to use FREETEXTTABLE perhaps you could make use of CONTAINS instead? (I have to be honest, I've not used full text search myself).

It would appear you can query like this:

SELECT Name, Price FROM Product
WHERE CONTAINS(Name, 'ice')
 AND CONTAINS(Name, 'cream')