Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FULLTEXT problem Expand / Collapse
Author
Message
Posted Thursday, September 26, 2013 2:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1498719
Posted Thursday, September 26, 2013 2:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 5:57 AM
Points: 330, Visits: 700
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 2005/2008 DBA - MCTS/MCITP
Post #1498729
Posted Thursday, September 26, 2013 2:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1498730
Posted Friday, September 27, 2013 8:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1499396
Posted Tuesday, October 1, 2013 5:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 583, Visits: 875
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
Post #1500627
Posted Wednesday, October 2, 2013 2:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1500698
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse