CONTAINSTABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CONTAINSTABLE

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2009-09-14 : 14:15:50

I have a table with a full text index on 2 columns, col1 and col2. I am searching the table using:

SELECT DISTINCT
v.Visit_ID,Col1,Col2

from MyTable t
INNER JOIN CONTAINSTABLE(dbo.MyTable,*
,N'drown
&! suicidal
&! suicide
&! psych
') k
ON t.Visit_ID = k.[key]

I am finding that it is only excluding records where "drown" and any of the AND NOT terms are in the same column. So, if "suicide" appears in Col1 and drown in col2, the record is not excluded. However if col1 contains anything and col2 contains - thoughts of "suicide", wants to "drown" himself - then the record is exluded.

Is this normal behavior or do I have something wrong in my query? If this is normal, is there another way to make it work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-14 : 14:28:22

i dont think it will look for words over different columns. it will just return you the rows with at least any column satisfying above condition i.e not having drown,suicidal,... words

Dennis Falls
Starting Member

41 Posts

Posted - 2009-09-14 : 14:31:10

But isn't that the point of having a full text index over multiple columns?

Dennis Falls
Starting Member

41 Posts

Posted - 2009-09-15 : 11:31:04

For anybody interested, my workaround for this is:

SELECT DISTINCT
v.Visit_ID,Col1,Col2

from MyTable t
INNER JOIN CONTAINSTABLE(dbo.MyTable,*
,N'drown
') k
ON t.Visit_ID = k.[key]
WHERE NOT CONTAINS(*,'suicidal | suicide | psycy')

Can anybody see any problems with doing it like that? It seems to work properly and still has good performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources