LIKE vs. FTS Results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Parenteau - 08 Nov 2007 15:28 GMT

Hi guys!

Hard to find exactly the definition of what I'm looking for in BOL. Let's
explain by example:

This return one row:
SELECT * FROM ContainsTable(Request,*,N'ISSI-2007-01001')

This return no row:
SELECT * FROM ContainsTable(Request,*,N'ISSI-2007-0100')

By using a LIKE with appropriate columns (Those that are Full-Text indexed),
I guess I would get row in each if I use %%.

However, I need to know (found the proof) if using CONTAINSTABLE does search
for the exact word if we do not use wildcards and double quotes. According to
my results, if we do not use wild cards, the function search for exact words
in the contains_search_condition (space seperating the words)

Does someone could confirm that to me and send/indicate where I can find
documented examples or rules for this?

Thanks a lot guys!

David, MCDBA

Reply to this Message

Hilary Cotter - 09 Nov 2007 12:21 GMT

Have you tried this?

select * from david where contains(*,'"ISSI-2007-0100*"')
GO
this will return 01001 and 0100

By default contains is an exact match, a search on ISSI-2007-0100 with not
match with ISSI-2007-01001

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

> Hi guys!
>
[quoted text clipped - 25 lines]
>
> David, MCDBA

Reply to this Message

David Parenteau - 09 Nov 2007 13:59 GMT

Thanks a lot Hilary for both replies to my threads! By the time I got your
answers, my tests (lot of them) have shown me what you tell. I also found
that the word 0 is a noise word in Neutral Language. This may explain the
multiple rows result from your example.

The only thing that was weird is that all 1 character noise word in the
noise data file are in the same line, seprated by a space. All other noise
word are in one line each. I guess this does not make differenc to the word
engine, but I did not found documentation on this particular part.

This exercise make me understand Full-Text search and how to use it... I
would use it for long text columns only :)

Again, thanks a lot!

> Have you tried this?
>
[quoted text clipped - 34 lines]
> >
> > David, MCDBA

Reply to this Message

Hilary Cotter - 09 Nov 2007 15:36 GMT

Yes, that is puzzling to me as well. I suspect it is so that you can remove
all the one letter characters words in one delete statement.

Signature

> Thanks a lot Hilary for both replies to my threads! By the time I got your
> answers, my tests (lot of them) have shown me what you tell. I also found
[quoted text clipped - 54 lines]
>> >
>> > David, MCDBA

Reply to this Message