I have successfully created an FTS index on a column where we have stored the name of medicines e.g:

Amoxicillin 250mg capsules 21 capsule
Verapamil 160mg tablets 100 tablet

When I search with the parameters Amoxicillin or tablets it works fine.

But when I search for '%Amoxi%' or blets it doesn't come up with the answer. However the same keywords work with Like operator.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxici%')

Above query has no results . however below query returns 140 rows in result.

select * from AMPP as a
WHERE Contains(A.[NM] ,'%Amoxicillin%')

select * from AMPP as a
WHERE a.nm like '%Amoxici%'

CONTAINS and LIKE are two completely different beasts.

As it is a full text index search the wildcards (% %) become redundant.

For your query I belive you would need to do something like:

WHERE CONTAINS(A.NM, '"Amoxici*"'); 

Refer to MS Books Online

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.