I have a table that contains 30 000+ records named as
ProductCodes that contains a column ‘
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
I have added a
UNION ALL query where it first selects the equal record and combining it with my
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.
Go to Source