'%phrase%' search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fayna - 22 Oct 2007 11:12 GMT

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

Reply to this Message

Daniel Crichton - 22 Oct 2007 11:43 GMT

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
[quoted text clipped - 3 lines]
> 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.

Signature

Dan

Reply to this Message

Hilary Cotter - 23 Oct 2007 01:55 GMT

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.

Signature

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

> fayna wrote on Mon, 22 Oct 2007 03:12:00 -0700:
>
[quoted text clipped - 27 lines]
> FTS will only ever work like LIKE if you are searching for specific whole
> single words or phrases.

Reply to this Message

Daniel Crichton - 23 Oct 2007 14:19 GMT

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.

Signature

Dan

Reply to this Message