FREETEXT / CONTAINS question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

admlangford@gmail.com - 28 May 2008 21:22 GMT

Hi, I am using the WITH FREETEXT command against my database.

The problem I am having is that the WITH FREETEXT treats seperate
words passed in using an OR

so instead of getting a search for 'Guitar AND Player AND America' I
am getting 'Guitar OR player OR America'

From looking around I can use the CONTAINS and build something like
this...

WITH CONTAINS(Titles,'Guitar) AND CONTAINS(Titles,'Player') AND
CONTAINS(Titles,'America')

However this seems a little excessive as I would have to split up the
string on spaces then dynamically create the necessary CONTAINS
Clauses (3 in the above example) and finally try different
combinations until I got a match (There different words above = 9
possible combinations).

There must be a better way :)

Any help appreciated

Thanks
Adam

Reply to this Message

Daniel Crichton - 29 May 2008 09:28 GMT

admlangford@gmail.com wrote on Wed, 28 May 2008 13:22:43 -0700 (PDT):

> Hi, I am using the WITH FREETEXT command against my database.

> The problem I am having is that the WITH FREETEXT treats seperate words
> passed in using an OR

> so instead of getting a search for 'Guitar AND Player AND America' I am
> getting 'Guitar OR player OR America'

> From looking around I can use the CONTAINS and build something like
> this...

> WITH CONTAINS(Titles,'Guitar) AND CONTAINS(Titles,'Player') AND
> CONTAINS(Titles,'America')

CONTAINS allows you to use AND in the term. You would only need to do the
above using FREETEXT.

SELECT * FROM MyTable WHERE CONTAINS(Titles,'Guitar and America')

Signature

Dan

Reply to this Message

Hilary Cotter - 29 May 2008 10:16 GMT

You should always wrap your search terms in double quotes for contains. For
example if you were searching on Guitar player and America (note the double
word first search term) you would have to search like this

select * from mytable where contains (*, '"guitar player" and America)

Otherwise your search would fail. For single word search tokens, as Daniel
has pointed out this will work.

> admlangford@gmail.com wrote on Wed, 28 May 2008 13:22:43 -0700 (PDT):
>
[quoted text clipped - 16 lines]
>
> SELECT * FROM MyTable WHERE CONTAINS(Titles,'Guitar and America')

Reply to this Message