Keyword searching using Full-Text Indexing (Getting Started)

Pete Sral

Creating a full-text catalog in SQL Server is easy, I used a tutorial on one of our favorite SQL Server blogs:

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

I created 4 catalogs:

Each catalog is assigned to a table and columns are selected to be included (indexed) in the full text searching:

Search syntax examples:

SELECT * From tbl_Matter Where Contains(*, ‘Plan and Test’)

Result where the exact word plan and test are contained in an indexed column:

SELECT * From tbl_Matter Where Contains(*, ‘Plan or Test’)

Result where the exact word plan or test are contained in an indexed column:

The FreeText search will find partial words containing plan or test in an indexed column:

SELECT * From tbl_Matter Where FreeText(*, ‘Plan Test’)

Result:

After creating a new row in a full text indexed table, the row is indexed and the text is available for searching.  For example, if I create the following record:

And searching immediately after saving using one of the following:

SELECT * From tbl_Matter Where Contains(*, 'Zeteq or lars')

SELECT * From tbl_Matter Where Contains(*, 'Zeteq')

SELECT * From tbl_Matter Where Contains(*, 'lars')

Result:

In conclusion, full-text searching offers an optimized way to do keyword searching on specific columns.  There are other syntaxes for full-text searching to search for specific data and even in positions.  More to come as we learn ways to integrate into NetTrax.Net.