Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql server 2008 r2 full text search on word breakers

Answered sql server 2008 r2 full text search on word breakers

  • Friday, February 17, 2012 11:52 AM
     
     
    Sign In to Vote
    0

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

     

All Replies

  • Monday, February 20, 2012 3:28 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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 8:08 AM
     
     
    Sign In to Vote
    0

    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:34 AM
    Moderator
     
     
    Sign In to Vote
    0
    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


     
  • Tuesday, February 21, 2012 5:19 PM
     
     
    Sign In to Vote
    0

    stephanie,

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

    nishin

     
  • Wednesday, February 22, 2012 7:23 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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