SQL Server Transact Basic to Expert - Search using Contains

This blog shows how to use CONTAINS in select query to search

Syntax:
SELECT TableColumns
From TableName
Where Contains (Columnname, "Search")


Example:
SELECT empid, empname
FROM dbo.emptable
WHERE CONTAINS (empname, '"Kar*"')