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
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
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dan Hirsch" <daniel.hirsch@.mckesson.com> wrote in message
news:1114200575.445065.76320@.f14g2000cwb.googlegro ups.com...
> 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
>
|||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
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment