Search for words in SQL Server index

full-text-search, linq, sql, sql-server

Solution

SQL Server 2000 or above.

SELECT *
  FROM dbo.TblBusinessNames
 WHERE BusinessName like '%[^A-z^0-9]Break%' -- In the middle of a sentence
    OR BusinessName like 'Break%'            -- At the beginning of a sentence

Keyword Reference for LIKE: http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx

Problem

I need something in between a full text search and an index search: I want to search for text in one column of my table (probably there will be an index on the column, too, if that matters). Problem is, I want to search for words in the column, but I don't want to match parts. For example, my column might contain business names: Mighty Muck Miller and Partners Inc. Boy & Butter Breakfast company Now if I search for "Miller" I want to find the first line. But if I search for "iller" I don't want to find it, because there is no word starting with "iller". Searching for "Break" should find "Boy & Butter Breakfast company", though, since one word is starting with "Break". So if I try and use ``` WHERE BusinessName LIKE %Break% ``` it will find too many hits. Is there any way to Search for Words separated by whitespace or other delimiters? (LINQ would be best, plain SQL would do, too) Important: Spaces are by far not the only delimiters! Slashes, colons, dots, all non-alphanumerical characters should be considered for this to work!

Original source