Contains Clause Issue

Contains Clause Issue - SQL 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dbalorenzini - 06 Nov 2008 21:01 GMT

The Full-Index Catalog is working fine and repopulating as it should. But the
query is not returning the expected results:

This is the string in the TicketNotes_Text column that in catalog is base on:

test00998877665544332211

If I run this I get the appropriate result back:

SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, '"*Test*" ')

But if I run this I return a empty recordset, I get records back but not my
test record:

SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, '"*0099*" ')

If you have any clues, I would be grateful.

Signature

Thanks,
Art
Database Administrator
Dealer Marketing Services, Inc..
arthurlorenzini@promaxonline.com
309.314.2176

Reply to this Message

Russell Fields - 06 Nov 2008 21:19 GMT

Art,

Here is a 2005 link, but it is the same in 2000 and 2008.
http://technet.microsoft.com/en-us/library/ms187787(SQL.90).aspx

Full-text search only supports "the prefix of a word or phrase" not post-fix
or in-fix. Therefore, it will only process the * at the end of a string.
So,

*Test* = Test*
*0099* = 0099*

If you need to search for 0099, you will need to combine full-text search
with a wildcard search. E.g.
TextColumn LIKE '%0099%'

RLF

> The Full-Index Catalog is working fine and repopulating as it should. But
> the
[quoted text clipped - 20 lines]
>
> If you have any clues, I would be grateful.

Reply to this Message

dbalorenzini - 06 Nov 2008 21:29 GMT

So I took yur suggestion and tried the following statement:

SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text FROM
DMS_TICKETNOTES
WHERE Contains(TicketNote_Text, ' %0099% ')

But this returned nothing.

Again the string is actually test00998877665544332211

Signature

Thanks,
Art
Database Administrator
Dealer Marketing Services, Inc..
arthurlorenzini@promaxonline.com
309.314.2176

> Art,
>
[quoted text clipped - 38 lines]
> >
> > If you have any clues, I would be grateful.

Reply to this Message

Russell Fields - 06 Nov 2008 21:59 GMT

Art,

Sorry, but I was apparently not clear. My example did not use CONTAINS. It
was more like this:

SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text
FROM DMS_TICKETNOTES
WHERE TicketNote_Text LIKE ' %0099% '

In this case the CONTAINS is meaningless since you have no data to match.
But you could be searching complimentary data such as below where fulltext
index and string search combine to get your answer:

SELECT ticket_id,ticketnote_id ticketnote_byname, Ticketnote_text
FROM DMS_TICKETNOTES
WHERE CONTAINS (TicketNote_Text, 'Johns* ')
AND TicketNote_Text LIKE ' %0099% '

So, my point was where a particular wildcard could be applied.

RLF

> So I took yur suggestion and tried the following statement:
>
[quoted text clipped - 53 lines]
>> >
>> > If you have any clues, I would be grateful.

Reply to this Message