Searching for Multiple Forms of Words or Phrases (Prefix Term)
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 ice*.
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