locked
Differences between contains and containstable RRS feed

  • Question

  • User901909194 posted

    Hi,

    I have the following queries that should be technically equivalent:

    select * from ProductSearchIndexData psid
    where Product_ID = 946 and contains(psid.[Text], '("exp*")')

    SELECT [key] as id, rank
    FROM CONTAINSTABLE(ProductSearchIndexData, [Text], '("exp*" )')
    where [key]= 946

    The first one returns the right result, the second returns none.

    Is there anything I am missing here?

    Thanks!

    Tuesday, April 29, 2014 12:29 PM

All replies

  • User1918509225 posted

    <o:p></o:p>

    Hi ebucis,

    contains is a conditional predicate and used in a where clause to search columns containing character-based data types.<!--?xml:namespace prefix = "o" ns = "urn:schemas-microsoft-com:office:office" /--><o:p></o:p>

    This term looks for a match based on a particular word or phrase which is provided in the sql query. <o:p></o:p>

    Basically it looks for an exact match, but it can be extended or modified to look for the inflectional matches.<o:p></o:p>

    While containsable  term is useful when we want to sort the resulted search output records by rank. <o:p></o:p>

    Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

    And In your containstable just contains  two field  one is the KEY ,the other is RANK,

    the  KEY column  that contains full-text key values. Each full-text indexed table has a column whose values are guaranteed to be unique, and the values returned in the KEY column are the full-text key values of the rows that match the selection criteria specified in the contains search condition.

    So please make sure in the second query ,in the table which from containsable whether has the key whose value is 946.

    More details about containsable please refer to lin below:

    http://technet.microsoft.com/en-us/library/aa258229(v=sql.80).aspx

    Hope it can help you.

    Best Regards,

    Kevin shen.

    Wednesday, April 30, 2014 3:37 AM
  • User901909194 posted

    Kevin,

    Thank you for your reply. 

    I think I undrestand how the full text search works since I have been using it myself for years. My problem here has to do with the fact that I execute both queries one after the other and I get different results. 

    It was my understanding that for this case they were equivalent. Is there anything different on how they execute that could be the reason for the result mismatching?

    Thanks again

    Wednesday, April 30, 2014 7:06 AM
  • User1918509225 posted

    Hi ebucis,

    For your problem is about sql sentence,for here is about data control forum,i suggest that you should post your thread to sql server forum  link below,they can provider a better answer for you.

    http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition

    Tuesday, May 6, 2014 2:20 AM