locked
Full-Text Index problem on SQL 2008 EE RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    When running the following query it only returns one result.

    select * from jobplan
    where ( contains(description, '  FORMSOF(INFLECTIONAL,Metal)  ') );

    However when I run the following query five results are returned.

    select * from jobplan
    where description like 'metal%';

    I'm not sure why this is not working? A full text index is setup for the description field and the catalog if populated.

    Any help would be appreciated.

    Thanks,

    Tyler

    Wednesday, January 8, 2014 8:01 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Tyler,

    Is your FTS index maybe out of date?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Tyler Darkow Thursday, January 9, 2014 3:12 PM
    Thursday, January 9, 2014 3:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Olaf,

    The index was up to date. However I decided maybe I should try to rebuild the catalog to see if that helped and it did, now the results are showing up as expected.  Thanks for your help

    Tyler

    • Marked as answer by Tyler Darkow Thursday, January 9, 2014 3:12 PM
    Thursday, January 9, 2014 3:12 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Tyler,

    You can use the system TVF for FTS parser to check which keywords will be used for the search:

    SELECT *
FROM sys.dm_fts_parser('FORMSOF(INFLECTIONAL,Metal)', 1033, 0, 0) AS FTS

    Returns:

    metal's
metaled
metaling
metalled
metalling
metals
metals'
metal

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Proposed as answer by Sofiya Li Thursday, January 9, 2014 10:00 AM
    • Edited by Olaf HelperMVP Thursday, January 9, 2014 12:34 PM
    Thursday, January 9, 2014 9:11 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Olaf,

    Here are the results of the query:

    Let me know if I am incorrect but it almost looks like the index is not working correctly as it appears the index is only finding 2 results instead of the 5 it should.

    Here are the results of the original query:

    Thanks
    Thursday, January 9, 2014 2:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Here are the results of the query that returns 5 results:

    Thanks

    Thursday, January 9, 2014 2:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Tyler,

    Is your FTS index maybe out of date?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Tyler Darkow Thursday, January 9, 2014 3:12 PM
    Thursday, January 9, 2014 3:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Olaf,

    The index was up to date. However I decided maybe I should try to rebuild the catalog to see if that helped and it did, now the results are showing up as expected.  Thanks for your help

    Tyler

    • Marked as answer by Tyler Darkow Thursday, January 9, 2014 3:12 PM
    Thursday, January 9, 2014 3:12 PM