Re: Contains clause with only NOT keywords

Tech-Archive recommends: Speed Up your PC by fixing your registry

Thanks Hilary,

But just a little clarification please...

When you say

"No, you can't do this. "

Did you mean my code example as suggested by ML (and which is currently
written and operating in test mode)?

Or were you refering to my question of using the * in the syntax.

Sorry to bother you again, but I will be putting the code into production
soon...

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:O5XHgZuIIHA.4272@xxxxxxxxxxxxxxxxxxxxxxx
No, you can't do this. Basically this is like saying, search on every word
in the dictionary and return hits to every document and then subtract the
ones which contain cow or bull.

--
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
"John Kotuby" <JohnKotuby@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OhP%23LstIIHA.2100@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Hilary...

I guess I confused the issue by mentioning the Date comparison in my
post. The crux of the matter was whether I could use JUST exclusions in
the Contains clause. For example...

contains((description),'(!"cow" &! "bull")')

The idea being that any record is acceptable, as long as the
'description' field does NOT contain either "cow" OR "bull"

In your syntax I guess that would translate into
contains((description), 'NOT ( "cow" AND "bull")').

Someone in the SQLServer.Programming group suggested:

where NOT(contains((description),'"cow" OR "bull"'))
and <the rest of the conditions>


That seems to work well, and now I use it whenever the program sees that
there were no keywords entered into any of the text boxes for each of
the conditions "With ALL of the Words:" or "With at Least ONE of the
Words:",
and the user has placed entries only in "With NONE of the Words:".

My code now becomes (when only exclusions are entered):

Where NOT (contains((description),'("cow" | "bull")') OR
contains((long_name),'("cow" | "bull")') )
AND ( list_date > '10/01/2007')

Note here that the date comparison is completely separated from the
Contains clause, as you have indicated.

However, I have noticed that you use the * in your contains example,
whereas I am creating a "contains" for each Full Text Indexed field being
queried.

Does this mean I can consolidate the query I am using with * instead of
querying each field separately? In my Text Catalog, only the 2 fields
"description" and "long_name" are indexed.

Thanks again...

"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message
news:%23WLKfysIIHA.4592@xxxxxxxxxxxxxxxxxxxxxxx
You have to parse your query so that it looks like this:

select * from John where contains(*,'("cat" AND "dog" AND "horse") AND
NOT ( "cow" AND "bull")')

I have upper cased the boolean operators for clarity.

For your date query it would look like this

select * from John where contains(*,'("cat" AND "dog" AND "horse") AND
NOT ( "cow" AND "bull")')
where orderdate>'2007-01-01'

You cannot search on a date string and hope for it to be interpreted as
a date and do inequality operations on it. So I could not do something
like this

select * from John where contains(*,'("cat" AND "dog" AND "horse") AND
NOT ( "cow" AND "bull") and OrderDate>'2007-01-01')

as sql FTS can only interpret the date string as a string and only do
not equal or equal operations against it.

--
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
"John Kotuby" <JohnKotuby@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23MZFHLUIIHA.5352@xxxxxxxxxxxxxxxxxxxxxxx
Hello everyone,

I posted this on sqlserver.programming and it was recommended I try
this group.

I am designing a search screen that searches for keywords in Text
fields as
well as searching other related tables with fields like Date ranges and
other lookup code fields. One of our users asked why they can't use a
Date-Range search in conjunction with keywords NOT found in the free
text. I
have read that it is not possible to do with Contains.

For example, a standard keyword search might create this Contains
clause:
contains((desciption),'("cat" & "dog") and ("horse") &! "cow" &!
"bull"')

The users just want to use the &! "cow" &! "bull" part of the Contains
query
along with other more standard Where criteria, for example "and
OrderDate >
'10/10 2006' ".

I have tried to pass "noise" words for the first part of the Contains,
but
they are ignored.

I also tried separating out the NOT keywords into a series of " and not
description like 'bull%' " type filters, but the performance becomes
intolerably slow.

Is there any way to get around this problem? Maybe some crafty
trickery?

Thanks to all...











.

Relevant Pages

  • Re: Contains clause with only NOT keywords
    ... For your date query it would look like this ... as sql FTS can only interpret the date string as a string and only do not ... I am designing a search screen that searches for keywords in Text fields ... I also tried separating out the NOT keywords into a series of " and not ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Contains clause with only NOT keywords
    ... You have to parse your query so that it looks like this: ... as sql FTS can only interpret the date string as a string and only do not ... Date-Range search in conjunction with keywords NOT found in the free text. ... a standard keyword search might create this Contains clause: ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Contains clause with only NOT keywords
    ... The crux of the matter was whether I could use JUST exclusions in the ... For your date query it would look like this ... as sql FTS can only interpret the date string as a string and only do not ... I am designing a search screen that searches for keywords in Text fields ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Keyword(s) query.
    ... Create a search form with a place to put all the keywords. ... Dim LettAs String ... Behind the search button on the form i open the following query (You can ... Hope this dirty way of doing it helps. ...
    (comp.databases.ms-access)
  • Re: search 1000 keywords in doc.
    ... Dim strText As String ... 'Open your keywords document ... 'Close your keywords document ... For lngCounter = 0 to UBound ...
    (microsoft.public.word.vba.general)