'%phrase%' search   

Hi,
I'd like to know if it's possible to implement the search '%word|phrase%' 
with full text catalog.
I've tried with "...CONTAINS(FIELD,'"word|phrase"')" but I don't get the 
same result as using "... FIELD LIKE '%word|phrase%'".
Full text search is faster than LIKE search but it return less rows.

Thanks

date: Mon, 22 Oct 2007 03:12:00 -0700   author:   fayna

Re: '%phrase%' search   

fayna wrote  on Mon, 22 Oct 2007 03:12:00 -0700:

> Hi,
> I'd like to know if it's possible to implement the search
> '%word|phrase%'
> with full text catalog.
> I've tried with "...CONTAINS(FIELD,'"word|phrase"')" but I don't get
> the  same result as using "... FIELD LIKE '%word|phrase%'".
> Full text search is faster than LIKE search but it return less rows.

> Thanks

You cannot use a wildcard at the start of a word, only at the end. You can 
do the following, for instance

CONTAINS(FIELD,'"word*"')

which will find all words beginning with "word", which would include "word", 
"wordy", "worded", "wording", etc.

But you can't use the * at the start of a word.

Also, with a phrase the result may be unexpected because the * applies to 
each word in the phrase, so

CONTAINS(FIELD,'"my phrase*"')

willl match with "my phrase", "my phraser", "myer phrase", etc etc

FTS will only ever work like LIKE if you are searching for specific whole 
single words or phrases.

-- 
Dan

date: Mon, 22 Oct 2007 11:43:45 +0100   author:   Daniel Crichton

Re: '%phrase%' search   

also SQL FTS will throw out the middle character. It knows something is 
there, but not what. So it will match on

word!phrase
word$phrase
and
word phrase amoung others.

-- 
RelevantNoise.com - dedicated to mining blogs for business intelligence.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Crichton"  wrote in message 
news:eLfXthJFIHA.4752@TK2MSFTNGP04.phx.gbl...
> fayna wrote  on Mon, 22 Oct 2007 03:12:00 -0700:
>
>> Hi,
>> I'd like to know if it's possible to implement the search
>> '%word|phrase%'
>> with full text catalog.
>> I've tried with "...CONTAINS(FIELD,'"word|phrase"')" but I don't get
>> the  same result as using "... FIELD LIKE '%word|phrase%'".
>> Full text search is faster than LIKE search but it return less rows.
>
>> Thanks
>
> You cannot use a wildcard at the start of a word, only at the end. You can 
> do the following, for instance
>
> CONTAINS(FIELD,'"word*"')
>
> which will find all words beginning with "word", which would include 
> "word", "wordy", "worded", "wording", etc.
>
> But you can't use the * at the start of a word.
>
> Also, with a phrase the result may be unexpected because the * applies to 
> each word in the phrase, so
>
> CONTAINS(FIELD,'"my phrase*"')
>
> willl match with "my phrase", "my phraser", "myer phrase", etc etc
>
> FTS will only ever work like LIKE if you are searching for specific whole 
> single words or phrases.
>
> -- 
> Dan
>

date: Mon, 22 Oct 2007 20:55:24 -0400   author:   Hilary Cotter

Re: '%phrase%' search   

Hilary wrote  on Mon, 22 Oct 2007 20:55:24 -0400:

> also SQL FTS will throw out the middle character. It knows something is
> there, but not what. So it will match on

> word!phrase word$phrase and word phrase amoung others.

I think the word|phrase wasn't an explicit string, rather the | represents 
an optional value, ie. it would be looking for a word, or a phrase.

-- 
Dan

date: Tue, 23 Oct 2007 14:19:11 +0100   author:   Daniel Crichton