Problem with search query in my Sql Server stored proc

Question

Hi folks,

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?

Answer 1

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