Among other things, "CONTAINS" returns synonym, note the last item:
Quote:
CONTAINS can search for:
A word or phrase.
* The prefix of a word or phrase.
* A word near another word.
* A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
* A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").
from
CONTAINS (Transact-SQL)[
^]
++++++[Round 2]++++++
If are looking for pattern match, try
LIKE[
^]. E.g.
SELECT * FROM text_search WHERE textvalue LIKE '%Banana%'