Last post May 06, 2014 02:20 AM by Kevin Shen - MSFT
Apr 29, 2014 12:30 PM|ebucis|LINK
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?
Apr 30, 2014 03:37 AM|Kevin Shen - MSFT|LINK
contains is a conditional predicate and used in a where clause to search columns containing character-based data types.
This term looks for a match based on a particular word or phrase which is provided in the sql query.
Basically it looks for an exact match, but it can be extended or modified to look for the inflectional matches.
term is useful when we want to sort the resulted search output records by rank.
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.
In your containstable just contains two field one is the KEY ,the other is RANK,
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.
please make sure in the second query ,in the table which from containsable whether has the key whose value is 946.
details about containsable please refer to lin below:
Hope it can help you.
Apr 30, 2014 07:06 AM|ebucis|LINK
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?
May 06, 2014 02:20 AM|Kevin Shen - MSFT|LINK
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.