Performing Prefix Searches
You can use Full-Text Search to search for words or phrases with a specified prefix.
When conducting a prefix search, all entries in the column that contain text beginning with the specified prefix will be returned. For example, to search for all rows that contain the prefix top-, as in topple, topping, and top itself, the query looks like this:
USE AdventureWorks2008R2;
GO
SELECT Description, ProductDescriptionID
FROM Production.ProductDescription
WHERE CONTAINS (Description, ' "top*" ' );
GO
All text that matches the text specified before the asterisk (*) is returned. If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (DESCRIPTION, 'top*'), full-text search does not consider the asterisk to be a wildcard..
When the prefix term is a phrase, each token making up the phrase is considered a separate prefix term. All rows that have words beginning with the prefix terms will be returned. For example, the prefix term "light bread*" will find rows with text of either "light breaded," "lightly breaded," or "light bread", but will not return "Lightly toasted bread".