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 AdventureWorks;
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".