You can search columns for text that begin with a specified word or phrase. The specified text used for the search is called a prefix term.
When the prefix term is a word, all entries in the column that begin with the word will be returned. For example, to search for all rows that contain the word ice, as in ice, ice cream, or ice-shaved drinks, the query looks like this:
USE Northwind GO SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description, ' "ice*" ' ) 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, 'ice*'), full-text search considers the asterisk as a character and will search for exact matches to
When the prefix term is a phrase, each word 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."
USE Northwind GO SELECT Description, CategoryName FROM Categories WHERE CONTAINS (Description, ' "light bread*" ' ) GO