CODEWITHSUNDEEP
sql-server-2005searchfull-text-searchfreetext
Chris
Chris

Reputation: 6842

Can you perform an AND search of keywords using FREETEXT() on SQL Server 2005?

There is a request to make the SO search default to an AND style functionality over the current OR when multiple terms are used.

The official response was:

not as simple as it sounds; we use SQL Server 2005's FREETEXT() function, and I can't find a way to specify AND vs. OR -- can you?

So, is there a way?

There are a number of resources on it I can find, but I am not an expert.

Upvotes: 3

Views: 1306

Answers (3)

Rob Allen
Rob Allen

Reputation: 17749

I just started reading about freetext so bear with me. If what you are trying to do is allow searches for a tag, say VB, also find things tagged as VB6, Visual Basic, VisualBasic and VB.Net, wouldn't those values be set as synonyms in the DB's Thesaurus rather than query parameters?

If that is indeed the case, this link on MSDN explains how to add items to the Thesaurus.

Upvotes: 1

Jeff Atwood
Jeff Atwood

Reputation: 63979

OK, this change is in -- we now use CONTAINS() with implicit AND instead of FREETEXT() and its implicit OR.

Upvotes: 1

Martin Marconcini
Martin Marconcini

Reputation: 27246

As far as I've seen, it is not possible to do AND when using FREETEXT() under SQL 2005 (nor 2008, afaik).

A FREETEXT query ignores Boolean, proximity, and wildcard operators by design. However you could do this: 

WHERE FREETEXT('You gotta love MS-SQL') > 0
 AND FREETEXT('You gotta love MySQL too...') >  0

Or that's what I think :)

-- The idea is make it evaluate to Boolean, so you can use boolean operators. Don't know if this would give an error or not. I think it should work. But reference material is pointing to the fact that this is not possible by design.

The use of CONTAINS() instead of FREETEXT() could help.

Upvotes: 2

Related Questions