0

I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'.

I have made a query that'll return the records matching and containing a passed String using CONTAINS.

Select TOP 5 * from ProductCodes
WHERE CONTAINS(Value,'"pepper*"')

It does return the records contains 'Pepper' e.g. Pepper and Bayleaf, Peppermint, Dr. Pepper etc. I also have an exact record that only contains the exact string, 'Pepper' which is displayed in the mid of the results. I only need to select the TOP 5 of the records and also the exacting match of passed string.

I also tried to use the CONTAINSTABLE

SELECT Value, RANK FROM
ProductCodes PC
INNER JOIN
CONTAINSTABLE(ProductCodes,Value,N'pepper') AS KEY_TBL
ON PC.Value = KEY_TBL.[KEY]
ORDER BY RANK DESC

I tried using a WHERE rank = 1000 which I read somewhere that's the rank of the closest match but as I figured out the rank of the record 'Pepper' is 128 and phrases that have more than 1 word match have a higher rank.

I am fairly new to using FREETEXT and Semantics so I am still learning on it. How can I take the exact match and 'partial' match in my TOP 5? TIA

edit:

I have added a UNION ALL query where it first selects the equal record and combining it with my CONTAINS query.

SELECT TOP 5 * FROM (
 SELECT TOP 5 * From ProductCodes 
 WHERE Value = 'pepper'
 UNION ALL 
 Select TOP 5 * from ProductCodes
 WHERE CONTAINS(Value,'"pepper*"')
) as Value

Now, I can get the exacting match and partial match but wonder how it'll make my query slower.

0

Your latest query allows the same row to be returned twice. Also it,'s possible to limit the results considering a predefined weight for the match in order to get a more relevant output.

You should get a more precise result with this:

WITH CTE
AS
(
    SELECT 10000 AS RANK,
        P.id, 
        P.value 
    FROM ProductCodes P
    WHERE P.value = 'pepper'

    UNION

    SELECT k.RANK,
        P.id, 
        P.value 
    FROM ProductCodes P
        INNER JOIN  
          CONTAINSTABLE(ProductCodes, value, 'ISABOUT (pepper WEIGHT(0.2), 
            "pepper*" WEIGHT(0.1))',  
            5) AS K  
          ON P.id = K.[KEY]
    WHERE value <> 'pepper'
) 
SELECT TOP (5) RANK, id, value
FROM CTE
ORDER BY RANK DESC;

References:

Search for words or phrases using weighted values (Weighted Term)

Examples of Using RANK to Limit Search Results

WITH common_table_expression (Transact-SQL)

2
  • I find this more accurate in solving my problem but does using a Recursive in the database have any effect on the overall system performance?
    – bish-cuit
    Jun 3 '20 at 2:49
  • @bish-cuit, a Common Table Expression (CTE) is not recursive by itself, it can only become recursive if you call it on the 2nd query. You can check it on this answer where I actually used a recursive CTE. Notice that on the query right under the UNION ALL there's a INNER JOIN CTE_Locations which is the name of the defined CTE. That self reference makes the CTE recursive. The way I used the CTE to answer your question it works just like a temporary table.
    – Ronaldo
    Jun 3 '20 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.