Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / April 2005

Tip: Looking for answers? Try searching our database.

Exclude Words

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Dan Hirsch - 22 Apr 2005 21:09 GMT
I have built a functioning full text search tool on my companies
extranet site.  I use the containstable function.  I was curious what
the syntax or process would be to do a "without these words" box to
allow users to filter their search results better.

Thanks,

Daniel Hirsch
John Kane - 22 Apr 2005 21:31 GMT
Daniel,
You can use the NOT keyword in either your CONTAINS or CONTAINSTABLE query,
however, there are some restrictions on how it can be used, from the 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.

Below are two examples  CONTAINSTABLE:
use pubs
-- 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]

-- another example of using Mutiple columns (same table) with a NOT
condition:
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]

Hope that helps!
John
Signature

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

> I have built a functioning full text search tool on my companies
> extranet site.  I use the containstable function.  I was curious what
[quoted text clipped - 4 lines]
>
> Daniel Hirsch
Dan Hirsch - 22 Apr 2005 21:39 GMT
Thanks Allot John.  the NOT is exactly what I was looking for.  I
figured there was syntax like that, but I wasn't sure exactly.

Thanks Again,

Daniel Hirsch
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.