FULLTEXT problem

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

  • 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

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

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

  • 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]

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

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

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