Wanted to be able to search across multiple text columns at first thought about difficult joins and views, but then remembered that almost every modern database system has this builtin as default functionality and since I am having to do MS SQL Server databases for the most part this is how to add it in.
Create the Full Text Catalog
CREATE FULLTEXT CATALOG FTSearch;
Create the Full Text Index on the appropriate table now
CREATE FULLTEXT INDEX ON Review ( Name Language 1033, Email Language 1033, Comments Language 1033 ) KEY INDEX PK_ReviewID ON FTSearch; GO
Now you can do queries like this
Select * from Review Where Contains(*, '\"a*\"')
Wildcard matching on the beginning of a string does not seem to be supported from my tests so this
is OK but
Fails to match anything.
Kind of garbage, but ok enough without involving using a real full text search tool like Lucene.NET