Using Wildcards in the CONTAINS Predicate in Enterprise Search SQL Syntax
The CONTAINS predicate supports the use of the asterisk (*) as a wildcard character 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.
The first example matches documents that have any word in the Description column beginning with "serve".
…WHERE CONTAINS(Description, 'serve*')
The second example matches documents with any phrases in the Description 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(Description, 'comp serve*')