Contains clause with only NOT keywords

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...
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@.discussions.microsoft.com> wrote in message
news:%23MZFHLUIIHA.5352@.TK2MSFTNGP03.phx.gbl...
> 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...
>
>
|||FYI - the original thread can be found here:
[url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
As I understand it, he OP was trying to find out how to combine a negative
FTI search (using CONTAINS) with additional restrictions in the WHERE caluse.
I suggested building the condition using NOT(CONTAINS()).
ML
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
|||thanks ML - that is an interesting approach. That should work, but it would
be expensive if the results set was large.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:329CBEFC-00B7-4396-B16C-79C5B6461DCB@.microsoft.com...
> FYI - the original thread can be found here:
> [url]http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.program ming&mid=b965bf2b-ce05-4ad2-baee-47205465946b[/url]
> As I understand it, he OP was trying to find out how to combine a negative
> FTI search (using CONTAINS) with additional restrictions in the WHERE
> caluse.
> I suggested building the condition using NOT(CONTAINS()).
>
> ML
> --
> Matija Lah, SQL Server MVP
> http://milambda.blogspot.com/