In SQL you can use the LIKE operator to perform simple keyword searches.  For example:

SELECT CustomerID, CompanyName, ContactName
FROM   dbo.Customers
WHERE  CompanyName LIKE '%St%' OR ContactName LIKE '%St%'

Using the Northwind database, the script above would return 21 results:

CustomerID  CompanyName                  ContactName
----------------------------------------------------------------
ALFKI       Alfreds Futterkiste          Maria Anders
BERGS       Berglunds snabbköp           Christina Berglund
EASTC       Eastern Connection           Ann Devon
ERNSH       Ernst Handel                 Roland Mendel
FRANR       France restauration          Carine Schmitt
GALED       Galería del gastrónomo       Eduardo Saavedra
GROSR       GROSELLA-Restaurante         Manuel Pereira
HILAA       HILARION-Abastos             Carlos Hernández
HUNGC       Hungry Coyote Import Store   Yoshi Latimer
...

However, this can be slow because it usually involves a full table scan.  SQL Server has a much more efficient way to perform full-text searches, which utilizes the Microsoft Search Engine.  This involves creating a full-text catalog, which is external to the database (actually stored outside normal database structure) so it requires a little configuration but has the potential to significantly improve search performance.

Defining A Full-Text Index
To define a full-text index, just navigate to the table you want the index on using SQL Management Studio, right click on the table name, and choose "Define Full-Text Index..." as shown below:

A wizard will appear like the one shown below, and you will need to configure what you want to index and how you want SQL to keep that index up-to-date (remember ... it is stored outside the normal database structure, so SQL has to keep it in-sync).  Most the time you don't need to change the wizard's default selections, but the next few screen shots show how I configured a full-text index for Northwind's Customer table.

After you have the index created, you can use the FREETEXT, FREETEXTTABLE, CONTAINS, and CONTAINSTABLE keywords (for more info on the functionality that each provides go here).  You could rewrite the query from the earlier example to be more like this:

SELECT   C.CustomerID, C.CompanyName, C.ContactName, R.[Rank]
FROM     dbo.Customers C INNER JOIN
         CONTAINSTABLE(dbo.Customers, (CompanyName, ContactName), '"St*"') R ON C.CustomerID = R.[KEY]
ORDER BY R.[Rank] DESC

Which would yield the following results:

CustomerID  CompanyName                  ContactName      Rank
----------------------------------------------------------------
LAZYK       Lazy K Kountry Store         John Steel       112
VICTE       Victuailles en stock         Mary Saveley     112
HUNGC       Hungry Coyote Import Store   Yoshi Latimer    96
LETSS       Let's Stop N Shop            Jaime Yorres     96
QUICK       QUICK-Stop                   Horst Kloss      96

The script using LIKE returned 21 results, but the one using the full-text index only returned 5 results.  However, that is actually a good thing, because the full-text index only returned relevant results (the ones someone would most likely be looking for if they searched for "St").  The LIKE clause returned any row that had "st" somewhere in it ... whether it was the first of a word or buried in the middle of it.  The full-text index is smart enough to only return words that start with "St," but notice that doesn't necessarily mean it is at the start of the string or preceded by whitespace ... because it returned "Quick-Stop" as well.  A hidden benefit of using full-text indexes is that the Microsoft Search Engine will intelligently parse the text, and yield more relevant results.

When you search a full-text index using the CONTAINSTABLE method, you are also able to utilize a new column named rank.  This column indicates how relevant the match is compared to the rest of the results.  In this example, the rank column isn't too useful.  But if you were searching a long product description, this column could become very useful.  I chose this really simple example to just show the bare bones functionality of searching SQL, but when you are searching columns with a lot of text (like a product description) ... that is when this approach really pays off in terms of efficiency, only returning relevant results, and providing a rank of how relevant a result was in comparison to the rest of the set.