Sql-server – Does full text search works with characters

full-text-searchsql-server-2008

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%'

Best Answer

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:

SELECT * 
FROM AMPP AS A
WHERE CONTAINS(A.NM, '"Amoxici*"');

Refer to MS Books Online

Related Question