You can search a word or phrase in which the word or words begin with specified text. In the case of a phrase, each word within the phrase is considered a prefix term. For example, to search for all rows that contain a description of ice, ice cream, or ice-shaved drinks, the query looks like:
USE Northwind
GO
SELECT Description, CategoryName
FROM Categories
WHERE CONTAINS (Description, ' "ice*" ' )
GO
Note These query examples will not find any matches in the rows of the Categories table that is installed with Microsoft® SQL Server™.
All text that matches the text specified before the asterisk (*) is returned. This example searches for those 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