2012年3月7日星期三

Full Text "And Not" Fails

I have set up a simple "ProdTest1" table with 3 columns: ID (int) which is an Identity column and indexed, Name (nvarchar 45), and Description (nvarchar 2000). I have then entered a single row with the following data:
ID: 1
Name: Purple Black Blue
Description: Black Blue

I have then set up a Full-Text index based on the Name and Description columns.

When I execute the following query that row is returned in the query results:
select * from ProdTest1 where contains (*,'Black and not purple')

I would not expect any returned rows. Am I missing something?

I am using Microsoft SQL Server 2000 - 8.00.2039 (Enterprise Edition)

Thanks

Adatacorp,
Yes... You need to use double quotes for your words &/or phrases, for example:
"black" and NOT "purple" within the single quotes of the CONTAINS clause.

From BOL 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.

More specificly, try the following examples:

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]

select pub_id, pr_info from pub_info
where contains(*,'"moon"') and NOT contains(*,'"Binnet"')

Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||John,

Including double-quotes in the SQL does not solve the problem. Both of the following two SQL statements incorrectly return my example row:

select * from ProdTest1 where contains (*,'black and not purple')
select * from ProdTest1 where contains (*,'"black" and not "purple"')|||

How about this version? Does it return the results you are expecting?

select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')

Additional, do you have more than one column FT-enabled? There are well known issues with searching "accross" columns when used with CONTAINS or CONTAINSTABLE, see KB article 294809 "FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns" at http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 for more details.

Finally, the OS platform that you have SQL Server installed and depending upon the text in your FT-enabled column, can be an issue as well. Does your FT-enabled column contain any punctuation characters in contact with your search words? Could you post the full output of SELECT @.@.version ?

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||

John,

You have answered the question.

I was expecting the full-text query to be applied across the concatenation of all the columns. But instead (per the KB article you reference), the query

select * from ProdTest1 where contains (*,'black and not purple')

is actually executed as...

select * from ProdTest1 where contains (Name,'black and not purple') OR contains (Description,'black and not purple')

Thus the Full-Text query correctly returns my row because the data in the Description column (Black Blue) meets the query requirement.

Indeed your suggested query:

select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')

does return the results I desire.

However for my desired behavior in all searches I think I will need to create yet another column and in a trigger concatenate all the query column data into that added column.

I wish Microsoft had an option to simply do the Full-Text searches on the catenation of all the columns as I desire.

Thanks for your help.

|||You're welcome, Bob,
I suspected so, as you were using the "*" (astrick) that indicates to search all FT-enabled column and behavior the you are seeing is the default behavior for SQL Server 2000.

However, for SQL Server 2005 (codename Yukon) supports a new syntax of multiple column syntax, for example:

SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')

The above query is functionally equivalent to the following SQL 2000 FTS query:

SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')

Specifically, an OR between columns is supported in a single statement, but
not an AND between columns and to the best of my knowledge NOT is not supported within this new syntax. I'll confirm this as well.

As for doing FT Searches on concatenated columns, I suspect you'll have to wait for the next version of SQL Server (post-Yukon) as IBM's DB2 Text Information Extender can do this somewhat via a UDF. Sad

Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

没有评论:

发表评论