FULLTEXT problem

Question

Post reply

FULLTEXT problem

PiMané

SSChampion

Points: 13965
More actions
September 26, 2013 at 2:32 am

#299150

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenny Jozi SSCrazy Points: 2028 More actions · Answer 1

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é SSChampion Points: 13965 More actions · Answer 2

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é SSChampion Points: 13965 More actions · Answer 3

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 SSCrazy Eights Points: 9938 More actions · Answer 4

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[/url]

PiMané SSChampion Points: 13965 More actions · Answer 5

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