locked
how to make SQL FTS to search in between word of phrase RRS feed

  • Question

  • Hai,

    Can we make FTS Contains keyword to search as LIKE. say I am having the following values

    melborne

    melons

    camel

    I am getting all the three values in a Query constructed with LIKE operator but in FTS I am getting only melborne and melons. Can any one suggest me what change should I make to bring all the above values using FTS CONTAINS

    Best regards,

    Vishwa.

     


    Vishwa
    Friday, December 3, 2010 1:31 PM

Answers

  • Hi Vishwa,

    Could you please show us your queries?  Do you enclose the search condition in double quotation marks ("") and add an asterisk (*) before the ending quotation mark as follows?

    SELECT BookID,BookTitile 
    FROM Books 
    WHERE CONTAINS(BookTitile,' "Chain*" ') 
    

    All records have a match of words or phrases beginning with Chain will be returned. However, the asterisk can only be added at the end.

    I think the behavior is working as intended. Full-Text Search is word and phrase-based but LIKE predicate can only return the exact matches of the word or phrase. Please also see: Full-Text Search Overview 

    We have to use LIKE instead. Alternatively, we can use Full-Text Search to perform a fuzzy search and then use LIKE to return the records that have an exact match of our search conditions. In this way, we can still take advantage of Full-Text Search to return results efficiently.

    References:

    Full-Text Search (SQL Server)
    http://msdn.microsoft.com/en-us/library/ms142571.aspx

    SQL Server 2008 Full-Text Search: Internals and Enhancements
    http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by KJian_ Thursday, December 9, 2010 11:21 AM
    Monday, December 6, 2010 8:40 AM