CONTAINSTABLE - weird results - using "and not"

Hello everyone,
I use full text search using containstable for search on my intranet
site. Its been working wonderfully. However, I have recently been
working on an upgrade to my search page to allow users to exclude
words. When excluding words I use the "and not" operator. I have
noticed that with some words it works, and with others it does not.
None of my words are noise or ignored words.
The below query returns 6 results (not using the excludes):
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )') KEY_TBL ON
FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN ICDB.dbo.LU_SearchCategories
LU_SearchCategories ON FT_TBL.Category = LU_SearchCategories.TypeID
WHERE FT_TBL.PermID <= (Select Users.Role from InfoCenter.dbo.Users
Users where Users.UID = 5432) and Category in (2,3) ORDER BY
KEY_TBL.RANK DESC
The top results in the query above returns a record that also contain
the words calculations and also the word integer. When I exclude
either of these words...it doesn't exclude that results from the
results.
Example using "and not"
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from ICDB.dbo.Users Users where Users.UID = 5432) and Category in (2,3)
ORDER BY KEY_TBL.RANK DESC
Further...when I include the word "calculations" in the search query as
a required word...it doesn't pull the record...actually..it doesn't
pull any records.
Example query:
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" ) AND (
"calculations*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
The words "integer" and "calculations" are not the only words it does
this on...there are others.
Of course as I stated previously...some words to accurately exclude
those results...as in this case with the word "clmfmtdta". Query
example below.
Select FT_TBL.UID as ID, FID, Category, Link, target, Title, SubTitle,
Description, LastUpdate, LU_SearchCategories.TypeName,
LU_SearchCategories.TypeShort, KEY_TBL.RANK FROM ICDB.dbo.SearchTable
FT_TBL INNER JOIN CONTAINSTABLE(ICDB.dbo.SearchTable, *, '(( "rte*" )
AND ( "billing*" ) AND ( "opt*" ) AND ( "editor*" )) and NOT (
"clmfmtdta*" )') KEY_TBL ON FT_TBL.UID = KEY_TBL.[KEY] INNER JOIN
ICDB.dbo.LU_SearchCategories LU_SearchCategories ON FT_TBL.Category =
LU_SearchCategories.TypeID WHERE FT_TBL.PermID <= (Select Users.Role
from InfoCenter.dbo.Users Users where Users.UID = 5432) and Category in
(2,3) ORDER BY KEY_TBL.RANK DESC
Does anybody have any ideas as to why this is doing this? Or maybe a
better way to use the "and not" operator?
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchT=ADable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Daniel,
Yes, it does. Unfortunately, the behavior is the "default" behavior for SQL
Server 2000 as SQL Server 7.0 was "fixed" to correspond to this same
behavior, i.e.., FT Search across column with or without the NOT
qualifier... Checkout the following two KB articles:
286787 (Q286787) FIX: Incorrect Results From Full-Text Search on Several
Columns
http://support.microsoft.com/default...b;en-us;286787
294809 (Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search
Across Columns
http://support.microsoft.com/default...b;en-us;294809
For a possible workaround to this behavior, see the following blog entry:
"SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
use Northwind
-- Multiple columns from one FT-enable table, modified to use the NOT
qualifier:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, '"University" and NOT "Lawrence"') as
A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<daniel.hirsch@.gmail.com> wrote in message
news:1123690134.517437.78570@.g43g2000cwa.googlegro ups.com...
I did a little more research...and I am thinking that because I use a
wildcard "*" to indicate the column, if say I used
CONTAINSTABLE(ICDB.dbo.SearchTXable, *, '(( "rte*" ) AND ( "billing*"
) AND ( "opt*" ) AND ( "editor*" )) and NOT ( "integer*" )')
Both rte, billing, opt, and editor would need to be in the same column
that integer is not in. So if rte, billing, opt, and editor were in
say the title column, and integer was in the description column...it
would not correctly filter out those records with integer in the
description.
Does this make sense? Any ideas?
|||Thanks..that does help...

contains/fuzzy/substring - type of join between two tables - MS SQL 2000

ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.
> country_name in your fields
> Will be not very efficient, though
Me no care :)|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
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
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>

contains/fuzzy/substring - type of join between two tables - MS SQL 2000

ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.

> country_name in your fields
> Will be not very efficient, though
Me no care |||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,
descriptionColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
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
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegroups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>

contains/fuzzy/substring - type of join between two tables - MS SQL 2000

ok, I have a table with names of countries.
I have another table with a description field.
I want to join the country_name with the description field, BUT
instead of the join being based on equality, I want it be based on
country_name appearing in the description field.
Is this possible?
You can try to use LIKE or PATINDEX in WHERE if these can find your
country_name in your fields
Will be not very efficient, though
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>
|||On Jun 29, 5:07 pm, "AlexS" <salexru200...@.SPAMrogers.comPLEASE>
wrote:
> You can try to use LIKE or PATINDEX in WHERE if these can find your
I dont think... AHA! a correlated subquery should do it! thanks.

> country_name in your fields
> Will be not very efficient, though
Me no care
|||You should also be able to use fulltext for this.
Here is an example
select * from TableWithName join (select [key] from
containstable(TableWithDescriptionField,descriptio nColumn, @.SearchPhrase))
as t
on t.[key]=TableWithName.pk
Assuming that the primary key column of TableWithDescriptionField is the
same value as the PK of the TableWithName (ie pk fk relationship).
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
"metaperl" <metaperl@.gmail.com> wrote in message
news:1183150658.978910.91270@.o61g2000hsh.googlegro ups.com...
> ok, I have a table with names of countries.
> I have another table with a description field.
> I want to join the country_name with the description field, BUT
> instead of the join being based on equality, I want it be based on
> country_name appearing in the description field.
> Is this possible?
>

CONTAINS, phrases and keywords

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?
>

contains(t 3) or contains(??)

Hi,
I am sure that my question was posted before but cannot find anything. So
here it is again:
1. Is there a way to search for "T 3" or "?" using contains without
changing the noise-text file?
2. No kind of escape character for the space in "T 3"?
Any help is really appreciated!
Thanks, Andreas
Andreas,
Unfortunately, no as the SQL Server/MSSearch wordbreaker does not allow
customization of wordbreaking characters.
If the search characters are anything except single letters &/or single
digit numbers, you can of course use a search phrase, i.e., multiple words
contained within double quotes.
Is your concern the ability to search on multiple single letters/digits
without getting error msg 7619" "The query contained only ignored words"?
Regards,
John
"Andreas" <nospam@.nospam.com> wrote in message
news:uZ6Ss07eEHA.3428@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am sure that my question was posted before but cannot find anything. So
> here it is again:
> 1. Is there a way to search for "T 3" or "?" using contains without
> changing the noise-text file?
> 2. No kind of escape character for the space in "T 3"?
> Any help is really appreciated!
> Thanks, Andreas
>
|||Completely right. There are some data in the database with names like "T 3"
or "M I B". The contains ('"T 3"') or contains ('"M I B"') gives exactly
7619 error msg. Any workarounds?
Thanks, Andreas
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:uVdDKT8eEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Andreas,
> Unfortunately, no as the SQL Server/MSSearch wordbreaker does not allow
> customization of wordbreaking characters.
> If the search characters are anything except single letters &/or single
> digit numbers, you can of course use a search phrase, i.e., multiple words
> contained within double quotes.
> Is your concern the ability to search on multiple single letters/digits
> without getting error msg 7619" "The query contained only ignored words"?
> Regards,
> John
>
> "Andreas" <nospam@.nospam.com> wrote in message
> news:uZ6Ss07eEHA.3428@.TK2MSFTNGP11.phx.gbl...
So
>
|||You're welcome, Andreas,
One possible solution, depending upon the OS platform you have SQL Server
installed on (post @.@.version), and how you display the film titles to your
users, is to embed html tags next to or touching or in contact with the
leading & trailing single letters, such as <b>M I B<\b> as this could solve
you problem with Win2K. If you're using WinXP or Win2003, this solution will
not work, and I'd recommend adding a special keyword or phrase to search on
for these known single letter movie titles, such as MIB or T3 and capture
these special single letter combinations when imputed from the searcher.
Also, if you want you can email me for non-MSSearch solutions as I have
developed some FTS solutions (using a movie database too) for a book project
I've researched & developed over the past 2 years...
Thanks,
John
"Andreas" <nospam@.nospam.com> wrote in message
news:uXNB7mRfEHA.236@.tk2msftngp13.phx.gbl...
> Completely right. There are some data in the database with names like "T
3"[vbcol=seagreen]
> or "M I B". The contains ('"T 3"') or contains ('"M I B"') gives exactly
> 7619 error msg. Any workarounds?
> Thanks, Andreas
>
>
> "John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
> news:uVdDKT8eEHA.704@.TK2MSFTNGP09.phx.gbl...
words[vbcol=seagreen]
words"?
> So
>
|||Hi John
I sent you an email to jt-kane@.comcast.net - can you please let me know if
you got it ... I thought it would be better to continue our conversation
there.
Regards
Andreas
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:OHZpXncfEHA.644@.tk2msftngp13.phx.gbl...
> You're welcome, Andreas,
> One possible solution, depending upon the OS platform you have SQL Server
> installed on (post @.@.version), and how you display the film titles to your
> users, is to embed html tags next to or touching or in contact with the
> leading & trailing single letters, such as <b>M I B<\b> as this could
solve
> you problem with Win2K. If you're using WinXP or Win2003, this solution
will
> not work, and I'd recommend adding a special keyword or phrase to search
on
> for these known single letter movie titles, such as MIB or T3 and capture
> these special single letter combinations when imputed from the searcher.
> Also, if you want you can email me for non-MSSearch solutions as I have
> developed some FTS solutions (using a movie database too) for a book
project[vbcol=seagreen]
> I've researched & developed over the past 2 years...
> Thanks,
> John
>
> "Andreas" <nospam@.nospam.com> wrote in message
> news:uXNB7mRfEHA.236@.tk2msftngp13.phx.gbl...
> 3"
allow[vbcol=seagreen]
single[vbcol=seagreen]
> words
letters/digits[vbcol=seagreen]
> words"?
anything.[vbcol=seagreen]
without
>

Contains(@v1, @v2) Is this legal?

I am attempting to perform a contains of one variable string in another.
here is a simple example of what I am attempting to do, this should return
true, but I am not sure if this is a limitation of sql server, that it will
now allow a contains on two datatypes. Any ideas?
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if (Contains(@.t2, @.t1))
print 'true'
else
print 'false'
Thanks.Hi, kapsolas
You probably want to use the CHARINDEX function:
IF CHARINDEX(@.t1,@.t2)<>0 ...
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms186323.aspx
Razvan|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
>I am attempting to perform a contains of one variable string in another.
> here is a simple example of what I am attempting to do, this should return
> true, but I am not sure if this is a limitation of sql server, that it
> will
> now allow a contains on two datatypes. Any ideas?
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if (Contains(@.t2, @.t1))
> print 'true'
> else
> print 'false'
> Thanks.
Another solution:
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if @.t2 like '%' + @.t1 + '%'
print 'true'
else
print 'false'|||Raymond,
that is the solution I have implemented. Using the LIKE. I wanted to clean
it up a bit to make it more readable by using the Contains.
I'll play with the char index as recommended in the other post as well.
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
> Another solution:
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if @.t2 like '%' + @.t1 + '%'
> print 'true'
> else
> print 'false'
>
>|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> Raymond,
> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
> I'll play with the char index as recommended in the other post as well.
I have no experience with Contains.
This is the information I got in BOL:
...You can use the CONTAINS predicate to search a database for a specific
phrase. Of course, such a query can be written using the LIKE predicate.
However, many forms of CONTAINS provide far more text query capabilities
than can be obtained with LIKE. Additionally, unlike using the LIKE
predicate, a CONTAINS search is always case insensitive...
So, if you are not using the extra "query capabilities" of Contains, I
suggest you use one of the other solutions that you got for this post.
Of course, the best would be to test all solutions with your database and
data to find the one that performs the best.|||Thanks for that piece Raymond.
For now I'll use the LIKE and as soon as I have a bit more time i'll
investigate the contains a bit more.
Thanks for your help
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> I have no experience with Contains.
> This is the information I got in BOL:
> ...You can use the CONTAINS predicate to search a database for a specific
> phrase. Of course, such a query can be written using the LIKE predicate.
> However, many forms of CONTAINS provide far more text query capabilities
> than can be obtained with LIKE. Additionally, unlike using the LIKE
> predicate, a CONTAINS search is always case insensitive...
> So, if you are not using the extra "query capabilities" of Contains, I
> suggest you use one of the other solutions that you got for this post.
> Of course, the best would be to test all solutions with your database and
> data to find the one that performs the best.
>
>|||> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
I don't know why you think that's cleaner or more readable. I guess for
someone who has never used T-SQL and only used FTS, but I think that'd be a
pretty rare bird.
A