Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL
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 &lt;&gt; '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)