Be aware that the LIKE solution doesn't scale well. It guarantees a table scan, so on large tables it will be slow.

The solution that Thrasymachus proposed works against a column value instead of an expression, so it can use indicies. It will scale well against the largest tables you can create using SQL Server.

-PatP