I have the following stored proc :-
SELECT Id, Name
FROM FooBars
WHERE CONTAINS(Name, 'FORMSOF(Tesaurus, @query)')
Works fine when there is one word i the query: eg. foo* But it fails when I want to have more than one word which i'm trying to look for. eg. foo* bar* (this means any rows that have words that start with foo and start with bar).
What format does my @query argument need to look like, to enable multiple words for a Full Text Search with a Thesaurus?
As far as I know, if you want to search for two or more expressions, you need to concatenate those with either AND, OR or NEAR, something like this (straight from Books Online):
USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, '"chain*" OR "full*"');
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, 'bike NEAR performance');
GO
SELECT Description
FROM Production.ProductDescription
WHERE ProductDescriptionID <> 5 AND
CONTAINS(Description, ' Aluminum AND spindle');
If and how that would work together with your FORMSOF(...) expression is unclear to me - but I'm sure you could quickly try that, no?
SELECT Id, Name
FROM FooBars
WHERE CONTAINS(Name, 'FORMSOF(THESAURUS, "foo*")'
OR 'FORMSOF(THESAURUS, "bar*")')
Also make sure to spell "thesaurus" correctly in your FORMSOF () expression! :-)
Marc
