none
sql server 2008 r2 full text search on word breakers

    Question

  • hi,
    can anybody help me with full text searching .
    1.) i need 2 check based on word breaker
    2.)that best matched word should always be on Top.


    The problem am facing in 1 st part is that
    select *  from sys.dm_fts_parser (' "ales Manag" ', 1033, 0, 0) shows excat match

    wheras
    SELECT * FROM Person.ContactType WHERE CONTAINS(Name,'FORMSOF(freetext,"ales manag")')
    gives no results

    and for the second thing


    SELECT *
    FROM Person.ContactType AS Address INNER JOIN
    CONTAINSTABLE(Person.ContactType,Name,'FORMSOF(INFLECTIONAL,Manager)') AS KEY_TBL
    ON Address.ContactTypeID = KEY_TBL.[KEY]
    ORDER BY KEY_TBL.Rank

    am getting the same rank for all the retrieved records.Hence "Manager" is coming at last .

    am using sql server 2008 r2 with adventure works 2008 r2 as my db

    can some please suggest anything??

    Friday, February 17, 2012 11:52 AM

Answers

  • nishin,

    Actually, your requirement may not be available rely on the full-text search. NEAR is a bit rough to your requirement. You may consider using other user-defined judgments to determine the degree of match based on the result from CONTAINSTABLE. For example, to find out the starting position of ‘Manager’ in the column by CHARINDEX, or the position of word ‘Manager’ in all worlds in the column based on the space position. It depends the expected result you needed.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, February 27, 2012 7:11 AM
    Wednesday, February 22, 2012 7:23 AM

All replies

  • Hi mishinK,

    >> SELECT * FROM Person.ContactType WHERE CONTAINS(Name,'FORMSOF(freetext,"ales manag")')

    First, full-text search does only support the prefix of a word or phrase, and ‘ales’ is a suffix of ‘sales’ which will not return any information.

    Second, FORMSOF with FreeText is used to find word variations combining with Thesaurus (find all words with the same meaning of a word) and inflectional (finds all of the tenses of a word).  For example, the following statement can find out records with ‘sales’ with the specified word ‘sale’:

    SELECT * FROM Person.ContactType WHERE CONTAINS (Name,'FORMSOF(freetext,"sale")')

    Could you please elaborate on your requirement a bit more? If you are trying to return records with ‘sale’ and / or ‘manager’, you can use the syntax below:

    SELECT * FROM Person.ContactType WHERE CONTAINS (Name,'FORMSOF(freetext,"sale")') OR --AND
    CONTAINS (Name,'FORMSOF(freetext,"Manager")')

    If you are attempted to get the result with ‘sale’ nearby ‘manager’,  please have a try with this syntax:

    SELECT * FROM Person.ContactType WHERE CONTAINS(name, '"sale*" NEAR manager');


    Stephanie Lv

    TechNet Community Support

    Monday, February 20, 2012 3:28 AM
  • Thanks stephanie that answers the first part of my Question

    but for the second part i inserted a dummy record Manager in my table person.contacttype

    (insert into person.ContactType
    values
    ('Manager',GETDATE()))

    so when am writing the query
    SELECT *
    FROM Person.ContactType AS Address INNER JOIN
    CONTAINSTABLE(Person.ContactType,Name,'FORMSOF(INFLECTIONAL,Manager)') AS KEY_TBL
    ON Address.ContactTypeID = KEY_TBL.[KEY]
    ORDER BY KEY_TBL.Rank

    its giving  the rank of all the rows as same ,but as manager is nearest match what i want is Manager should come on Top when result is displayed


    nishin

    Monday, February 20, 2012 8:08 AM
  • nishin,

    The result is reasonable. It is a
    character-based match for precise or fuzzy (less precise) matches to single words and phrases. For the example above, all of records returned are precise matched with the ‘Manager’ single word. The rank cannot represent additional words around the ‘Manager’ on each record. You may have a look at NEAR keyword in CONTAINS for this type of usage.

    Stephanie Lv

    TechNet Community Support


    Monday, February 20, 2012 8:34 AM
  • stephanie,

    i tried but am not able to get a different rank for the above.Can you please help?


    nishin

    Tuesday, February 21, 2012 5:19 PM
  • nishin,

    Actually, your requirement may not be available rely on the full-text search. NEAR is a bit rough to your requirement. You may consider using other user-defined judgments to determine the degree of match based on the result from CONTAINSTABLE. For example, to find out the starting position of ‘Manager’ in the column by CHARINDEX, or the position of word ‘Manager’ in all worlds in the column based on the space position. It depends the expected result you needed.


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, February 27, 2012 7:11 AM
    Wednesday, February 22, 2012 7:23 AM