Word Search in SQL Server Index

Question

I need something between full text search and index search:
I want to search for text in one column of my table (there will probably be an index in the column, if that matters).

The problem is that I want to search for words in a column, but I do not want to combine the details.

For example, my column may contain company names:
Mighty Muck Miller and Partners Inc.
Boy and butter for breakfast

Now, if I search for “Miller”, I want to find the first line. But if I search for “colder,” I don’t want to find it, because there is no word starting with “iller”. A search for “Break” should find the company “Boy and Butter for Breakfast,” though since one word begins with “Break.”

So, if I try to use

WHERE BusinessName LIKE %Break% 

he will find too many hits.

Is there a way to search for words separated by spaces or other delimiters ?

(LINQ will be better, plain SQL will do too)

Important: Spaces are far from the only delimiters! To do this, consider hyphens, colons, periods, all non-alphanumeric characters!

Answer 1

SQL Server 2000 or later.

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

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

Answer 2

There will be many word separators: space, tab, beginning of line, parentheses, periods, commas, exclamation points and question marks, etc. So a fairly simple solution is to use a regular expression in the WHERE clause. (And it will be much more efficient than just ORing for all the possible delimiters you can think of.)

Since you mentioned LINQ, it describes how to make efficient regular expression with SQL Server .

Complex WHERE clauses like this always raise a red flag with me as far as performance is concerned, so I definitely suggest benchmarking in which you will end up, you can decide to build a search index for the column in the end.

EDIT:. I saw that you edited your question. When writing your regular expression , it's easy to just use some non-alphanumeric character as a delimiter, i.e. [^ 0-9a-zA-Z] or \ W for any non-word character, \ b for any word boundary and \ B for any non-primary border. Or instead of matching separators, they simply match any word, i.e. \ W +. Here 's another example that makes regular queries with SQL Server (more complicated than what you need).

Answer 3

 where BusinessName like 'Break%' -- to find if it is beginning with the word or BusinessName like '% Break%' -- to find if it contains the word anywhere but the beginning 

Answer 4

 WHERE BusinessName LIKE '% Break%' 

Answer 5

You mentioned LINQ - you could do something like ...

 string myPattern = "% Break%"; var query = from b in Business where SqlMethods.Like(b.BusinessName, myPattern) select b; 

Note that this uses the System.Linq.Data.SqlClient namespace, which translates directly to the LIKE statement without additional processing.

Answer 6

Try the following:

 declare @vSearch nvarchar(100) set @vSearch = 'About' select * from btTab where ' ' + vText + ' ' LIKE '%[^Az^0-9]' + @vSearch + '[^Az^0-9]%'