I'm getting some results from a CONTAINS query that I find odd. Here
is MyTable:
ID TheText
1 two AND three
2 two three
and here is the query that I find odd:
SELECT * FROM MyTable WHERE CONTAINS(*, '"two OR three"')
that's a single quote followed by a double quote and then the reverse
at the end:
It returns row 1 but not row 2 in the results.
The only way I can explain why row 1 is being returned is if SQL is
throwing out both the AND in the data and the OR in the query. But if
that is the case why isn't row 2 being returned?
Anybody have any idea what's going on?
Hello ddaiker,
OR is an ignored word, what happens is that the ignored word gets replaced
by a token
so your search becomes "two ? three", similarly for the document and is an
ignored word, your search will only find a record where two and three appear
and are seperated by 1 ignored word.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> I'm getting some results from a CONTAINS query that I find odd. Here
> is MyTable:
> ID TheText
> --
> 1 two AND three
> 2 two three
> and here is the query that I find odd:
> SELECT * FROM MyTable WHERE CONTAINS(*, '"two OR three"')
> that's a single quote followed by a double quote and then the reverse
> at the end:
> It returns row 1 but not row 2 in the results.
> The only way I can explain why row 1 is being returned is if SQL is
> throwing out both the AND in the data and the OR in the query. But if
> that is the case why isn't row 2 being returned?
> Anybody have any idea what's going on?
>
Showing posts with label keywords. Show all posts
Showing posts with label keywords. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
CONTAINS function and OR
I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:
> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:
> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan
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/
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/
Subscribe to:
Posts (Atom)