|
|
You're in the
sql server full text search
group:Search in all indexed columns + operator "NOT"
sql server full text search:
The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS": "AND | AND NOT | OR - Specifies a logical operation between two contains search conditions. When <contains_search_condition> contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions: NOT is applied before AND. NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed. NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ). AND is applied before OR. Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order. While it is undocumente, you can also use the syntax (|, &, and !) for the OR, AND, NOT operators. The following are some examples of using a stored proc and NOT in SQL FTS queries: use pubs go -- DROP PROCEDURE usp_FTSearchPubsInfo CREATE PROCEDURE usp_FTSearchPubsInfo ( @vcSearchText varchar(7800)) AS declare @s as varchar (8000) set @s='select pub_id, pr_info from pub_info where contains(pr_info,'+''''+@vcSearchText+''''+')' exec (@s) go -- returns 2 rows EXEC usp_FTSearchPubsInfo '("books" and "publisher")' go -- returns 0 rows EXEC usp_FTSearchPubsInfo '("books" and not "publisher")' go -- returns 2 rows EXEC usp_FTSearchPubsInfo '("book*") and not ("food" or "work")' go -- returns 2 rows when NOT, not included and 0 rows when NOT is included SELECT p.pub_id, p.pr_info, c.[rank] from pub_info AS p, containstable(pub_info, *, '"books" and NOT "publisher"') as c where c.[KEY] = p.pub_id order by c.[rank] Hope this helps! John [quoted text, click to view] "Matthias Kientz" <nospam_kientz@funkinform.de> wrote in message news:eTAGEa62EHA.2572@tk2msftngp13.phx.gbl... > I have a fulltext indexed table (aTest) which look like this > (strCaption and strText are both fulltext indexed columns): > > lngStoryId strCaption strText > ----------- ------------------------ ------------------------------ > 1 Kerry Bush meets Kerry > > > Now, let's search: > > SELECT strCaption, strText > FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush') > > strCaption strText > ------------------------------ ------------------------------ > Kerry Bush meets Kerry > > (1 row(s) affected) > > > This is not what I've expected! > It seems to be true, that I'll get a result if my COMPLETE search term > matches ANY of the indexed columns. I would expect, that "NOT" excludes > such results - irrespective of the matching column. > > Is it really true, that SQL Server works like this? I've not found which > rules are used in this case in the server documentation. > > Thanks in advance > Matthias > >
try
SELECT strCaption, strText FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"') -- Hilary Cotter Looking for a SQL Server replication book? Now available for purchase at: http://www.nwsu.com/0974973602.html [quoted text, click to view] "Matthias Kientz" <nospam_kientz@funkinform.de> wrote in message news:eTAGEa62EHA.2572@tk2msftngp13.phx.gbl... >I have a fulltext indexed table (aTest) which look like this > (strCaption and strText are both fulltext indexed columns): > > lngStoryId strCaption strText > ----------- ------------------------ ------------------------------ > 1 Kerry Bush meets Kerry > > > Now, let's search: > > SELECT strCaption, strText > FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush') > > strCaption strText > ------------------------------ ------------------------------ > Kerry Bush meets Kerry > > (1 row(s) affected) > > > This is not what I've expected! > It seems to be true, that I'll get a result if my COMPLETE search term > matches ANY of the indexed columns. I would expect, that "NOT" excludes > such results - irrespective of the matching column. > > Is it really true, that SQL Server works like this? I've not found which > rules are used in this case in the server documentation. > > Thanks in advance > Matthias > >
I have a fulltext indexed table (aTest) which look like this
(strCaption and strText are both fulltext indexed columns): lngStoryId strCaption strText ----------- ------------------------ ------------------------------ 1 Kerry Bush meets Kerry Now, let's search: SELECT strCaption, strText FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush') strCaption strText ------------------------------ ------------------------------ Kerry Bush meets Kerry (1 row(s) affected) This is not what I've expected! It seems to be true, that I'll get a result if my COMPLETE search term matches ANY of the indexed columns. I would expect, that "NOT" excludes such results - irrespective of the matching column. Is it really true, that SQL Server works like this? I've not found which rules are used in this case in the server documentation. Thanks in advance Matthias
This works for me. Are the words kerrey and bush in the same column?
-- Hilary Cotter Looking for a SQL Server replication book? Now available for purchase at: http://www.nwsu.com/0974973602.html [quoted text, click to view] "Matthias Kientz" <nospam_kientz@funkinform.de> wrote in message news:ewXKRjU3EHA.2504@TK2MSFTNGP10.phx.gbl... > Hilary Cotter wrote: >> try >> >> SELECT strCaption, strText >> FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"') >> >> > > Thanks, but using double quotes makes no difference.
[quoted text, click to view]
Hilary Cotter wrote: > try > > SELECT strCaption, strText > FROM aTest WHERE CONTAINS(aTest.*,'"kerry" AND NOT "bush"') > >
[quoted text, click to view]
John Kane wrote: > Matthias, > The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS": > "AND | AND NOT | OR - Specifies a logical operation between two contains > search conditions. > When <contains_search_condition> contains parenthesized groups, these > parenthesized groups are evaluated first. > After evaluating parenthesized groups, these rules apply when using these > logical operators with contains search conditions: > NOT is applied before AND. > NOT can only occur after AND, as in AND NOT. The OR NOT operator is not > allowed. > NOT cannot be specified before the first term (for example, CONTAINS > (mycolumn, 'NOT "phrase_to_search_for" ' ). > AND is applied before OR. > Boolean operators of the same type (AND, OR) are associative and can > therefore be applied in any order. > That's all clear. My problem is the behavior of SQL Server while quering a fulltext catalog which contains more than one fulltext column. My query was: [quoted text, click to view] >>SELECT strCaption, strText >>FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush') >> >>strCaption strText >>------------------------------ ------------------------------ >>Kerry Bush meets Kerry >> >>(1 row(s) affected) Note that, strCaption and strText are both fulltext indexed. SQL Server seems to search internal like this: SELECT strCaption, strText FROM aTest WHERE ( CONTAINS(aTest.strCaption,'kerry and not bush') ) OR ( CONTAINS(aTest.strText,'kerry and not bush') ) But what I want to do is that (this is also my workaround): SELECT strCaption, strText FROM aTest WHERE CONTAINS(aTest.*,'kerry') AND NOT CONTAINS (aTest.*,'bush') ==> I have to parse the search string and check whether there is a "NOT" operator used, and use the workaround then. I'm not really sure, whether this is an error or not, but I think this
Matthias,
Thank you for provide more info as querying multiple FT-enabled columns that also include a NOT condition should be possible with the below example using the Pubs database, Authors table with the last name & city columns as an example. However, another important factor is the OS platform that you have SQL Server (2000 or 7.0 ?) installed. Could you post the full output of -- SELECT @@version use pubs go select pub_id, pr_info from pub_info where contains(*,'"moon"') and NOT contains(*,'"Binnet"') go -- or using CONTAINSTABLE SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city, KEY_TBL.RANK FROM authors as FT_TBL, CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL, CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1 WHERE FT_TBL.au_id = KEY_TBL.[KEY] or FT_TBL.au_id = KEY_TBL1.[KEY] /* -- returns: au_id au_lname au_fname city RANK ----------- ---------------------------------------- -------------------- -- ------------------ ----------- 267-41-2394 O'Leary Michael San Jose 80 (1 row(s) affected) */ You may have to alter the OR condition between the KEY_TBL & FT_TBL.au_id to an AND condition as well as need to use a DISTINCT qualifier in your select list as well to get the desired results that you want. Keep in mind that this is a workaround to a by design behavior for SQL Server 2000 as the SQL Server 7.0 behavior was *fixed* to conform to the default SQL Server 2000 behavior, documented in the following KB articles: 286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several Columns http://support.microsoft.com/default.aspx?scid=kb;en-us;286787 294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 This is not an error (or bug) as it is by design, but I would agree that this is a DOC bug, i.e., a documentation bug, as it is not a very well documented feature... Regards, John [quoted text, click to view] "Matthias Kientz" <nospam_kientz@funkinform.de> wrote in message news:OBpttmV3EHA.3416@TK2MSFTNGP09.phx.gbl... > John Kane wrote: > > Matthias, > > The following is from SQL Server 2000 BOL (Books Online) title "CONTAINS": > > "AND | AND NOT | OR - Specifies a logical operation between two contains > > search conditions. > > When <contains_search_condition> contains parenthesized groups, these > > parenthesized groups are evaluated first. > > After evaluating parenthesized groups, these rules apply when using these > > logical operators with contains search conditions: > > NOT is applied before AND. > > NOT can only occur after AND, as in AND NOT. The OR NOT operator is not > > allowed. > > NOT cannot be specified before the first term (for example, CONTAINS > > (mycolumn, 'NOT "phrase_to_search_for" ' ). > > AND is applied before OR. > > Boolean operators of the same type (AND, OR) are associative and can > > therefore be applied in any order. > > > > That's all clear. > My problem is the behavior of SQL Server while quering a fulltext > catalog which contains more than one fulltext column. > > My query was: > > >>SELECT strCaption, strText > >>FROM aTest WHERE CONTAINS(aTest.*,'kerry AND NOT bush') > >> > >>strCaption strText > >>------------------------------ ------------------------------ > >>Kerry Bush meets Kerry > >> > >>(1 row(s) affected) > > Note that, strCaption and strText are both fulltext indexed. > SQL Server seems to search internal like this: > > SELECT strCaption, strText FROM aTest > WHERE > ( > CONTAINS(aTest.strCaption,'kerry and not bush') > ) > OR > ( > CONTAINS(aTest.strText,'kerry and not bush') > ) > > But what I want to do is that (this is also my workaround): > > SELECT strCaption, strText FROM aTest > WHERE CONTAINS(aTest.*,'kerry') > AND NOT CONTAINS (aTest.*,'bush') > > ==> I have to parse the search string and check whether there is a "NOT" > operator used, and use the workaround then. > > I'm not really sure, whether this is an error or not, but I think this > is an unexpected and not very well documented "feature".
[quoted text, click to view]
Hilary Cotter wrote: > This works for me. Are the words kerrey and bush in the same column? > No, they're not in the same column. The table looks like this: lngStoryId strCaption strText ----------- ------------------------ ------------------------------ 1 Kerry Bush meets Kerry If I ask the whole index for "X AND NOT Y" (in my example "Kerry AND NOT Bush"), I would expect that all results are excluded which contain Y in ANY indexed column.
[quoted text, click to view]
John Kane wrote: > Matthias, > Thank you for provide more info as querying multiple FT-enabled columns that > also include a NOT condition should be possible with the below example using > the Pubs database, Authors table with the last name & city columns as an > example. However, another important factor is the OS platform that you have > SQL Server (2000 or 7.0 ?) installed. Could you post the full output of -- > SELECT @@version Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2) [quoted text, click to view] > > use pubs > go > select pub_id, pr_info from pub_info > where contains(*,'"moon"') and NOT contains(*,'"Binnet"') > go > -- or using CONTAINSTABLE > SELECT FT_TBL.au_id, FT_TBL.au_lname, FT_TBL.au_fname, FT_TBL.city, > KEY_TBL.RANK > FROM authors as FT_TBL, > CONTAINSTABLE (authors,city, '"jose" and NOT "city"' ) AS KEY_TBL, > CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1 > WHERE > FT_TBL.au_id = KEY_TBL.[KEY] or > FT_TBL.au_id = KEY_TBL1.[KEY] > /* -- returns: > au_id au_lname au_fname > city RANK > ----------- ---------------------------------------- -------------------- -- > ------------------ ----------- > 267-41-2394 O'Leary Michael > San Jose 80 > (1 row(s) affected) > */ > > You may have to alter the OR condition between the KEY_TBL & FT_TBL.au_id to > an AND condition as well as need to use a DISTINCT qualifier in your select > list as well to get the desired results that you want. Keep in mind that > this is a workaround to a by design behavior for SQL Server 2000 as the SQL > Server 7.0 behavior was *fixed* to conform to the default SQL Server 2000 > behavior, documented in the following KB articles: > > 286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several > Columns > http://support.microsoft.com/default.aspx?scid=kb;en-us;286787 > 294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search > Across Columns > http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 > > This is not an error (or bug) as it is by design, but I would agree that > this is a DOC bug, i.e., a documentation bug, as it is not a very well > documented feature... > Regards, > John Yes, we agree in that. It's a pitty, that the design does not distinguish between positive and negative logic in the query, so the behavior is not the intuitive way to search in a multi-column index. I think I have to take it as it is and use my workaround to get the results which are expected. Thanks for your help
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |