SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FULLTEXT problem


FULLTEXT problem

Author
Message
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
Hi,

I've a table with a fulltext index and when I search for an exact phrase I get a result but when I search for all the words with AND operator I get no results.
Is there something wrong with my fulltext?


SELECT * FROM CONTAINSTABLE(dbo.KNB_ItemCultures, *, '"How to migrate reports between different versions"')
-- get 1 result
SELECT * FROM CONTAINSTABLE(dbo.KNB_ItemCultures, *, '"How" AND "to" AND "migrate" AND "reports" AND "between" AND "different" AND "versions"')
-- no results
SELECT * FROM CONTAINSTABLE(dbo.KNB_ItemCultures, *, '"*How*" AND "*to*" AND "*migrate*" AND "*reports*" AND "*between*" AND "*different*" AND "*versions*"')
-- no results either



Thanks,
Pedro



If you need to work better, try working less...
Kenny Jozi
Kenny Jozi
Right there with Babe
Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)Right there with Babe (740 reputation)

Group: General Forum Members
Points: 740 Visits: 1340
Try something like this




SELECT *
FROM dbo.KNB_ItemCultures
WHERE CONTAINS(MyColumnName, '"*How*" OR "*to*" OR "*migrate*" OR "*reports*" OR "*between*" OR "*different*" OR "*versions*"');



SQL 2000/2005/2008/2012 DBA - MCTS/MCITP
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
kenneth.mofokeng (9/26/2013)
Try something like this
SELECT *
FROM dbo.KNB_ItemCultures
WHERE CONTAINS(MyColumnName, '"*How*" OR "*to*" OR "*migrate*" OR "*reports*" OR "*between*" OR "*different*" OR "*versions*"');



I don't want the records with any of the words... I want the records with all the words. This returns over 10.000 records and only less than 10 should be returned, with the exact text only one is returned.

Pedro



If you need to work better, try working less...
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
Just one more info...
On a 2012 11.0.3350 the fulltext returns the desired results (all 3 queries work fine) but on a 2012 (11.0.3000) the results are as I explained, only the exact search returns data.
The script used to create the fulltext was the same... Can this be an issue with different versions? A BUG fixed?

Thanks,
Pedro



If you need to work better, try working less...
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 979
Have you verified that you are not running into a stop word issue? Try setting the index on the table to ignore all stop words (including the system default ones):
ALTER FULLTEXT INDEX ON tablenameSET STOPLIST = OFF



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
PiMané
PiMané
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1020 Visits: 1334
Keith Tate (10/1/2013)
Have you verified that you are not running into a stop word issue? Try setting the index on the table to ignore all stop words (including the system default ones):
ALTER FULLTEXT INDEX ON tablename SET STOPLIST = OFF


Doing this the 1st and 3rd query return values, but the 2nd one should also return since it has all the words of the exact search.. shouldn't it?!
I also found a problem with our fulltext, it had change_tracking = auto and since it's a table that has lots of changes the catalog was always being rebuilt and many times didn't return the desired values...

Pedro



If you need to work better, try working less...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search