Using Wildcards in the CONTAINS Predicate
The CONTAINS predicate supports the use of the asterisk (*) as a wildcard for words and phrases. You can add the asterisk only at the end of the word or phrase. The presence of the asterisk enables the prefix-matching mode. In this mode, matches are returned if the column contains the specified search word with zero or more other characters. If a phrase is provided, matches are detected if the column contains all the specified words with zero or more other characters.
Examples
The first example matches documents that have any word in the FileName column beginning with "serve".
...WHERE CONTAINS(System.FileName, '"serve*"')
The second example matches documents with any phrases in the FileName column that begin with "comp" and in which the next word begins with "serve". For a document to match, it must match all the terms.
...WHERE CONTAINS(System.FileName, '"comp serve*"')
Note that the asterisk works only for prefix-matching; it does not work for suffix-matching. The following syntax does not match documents with any word in the FileName column ending with "serve".
WHERE CONTAINS(System.FileName, '"*serve"')