Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

Question

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.

Answer 1

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)