Full Text Search

SUMMARY

SQL Server provides a way using “Full Text Search” to search in a more sophisticated way ahead then normal exact character search. You can search by meaning (run, ran, running), by any order (Nisarg Kinariwala, Kinariwala Nisarg). Also you can search in multiple columns in a single shot.

INFO

- Only 1 Full Text Index is allowed per table/indexed view

- Contains column which have char/varhcar/varbinary text

- Table should have at least one Unique key or Primary Key

- You can select language for each column participating in Index

- Change Tracking Option has “Auto” and “Manual” options. When it is “Manual”, we have to populate the data periodically. “Auto” will take care as and when data changes in a column.

- Create Full Text Catalog à Create Full Text Index à Populate Index

HOW TO?

USAGE

1. SELECT * from TableName WHERE FREETEXT(ColumnName, 'RUN')

This will identify all the rows where ColumnName contains “run”/”ran”/”running” etc.

2. SELECT * from TableName WHERE FREETEXT(*, 'RUN')

This will identify all the rows where all columns (participating in index) contains “run”/”ran”/”running” etc.

3. SELECT * from TableName WHERE CONTAINS(ColumnName, 'nisarg AND kinariwala')

This will identify all the rows where columnName contains “nisarg” and “kinariwala” anywhere in the text in any order

4. SELECT * from TableName WHERE CONTAINS(ColumnName, 'nisarg OR kinariwala')

This will identify all the rows where columnName contains either “nisarg” or “kinariwala” or both anywhere in the text in any order