Full Text Search

Full-Text Search (Contains & Free Text )

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max)

FREE TEXT
 

The FREETEXT predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. The FREETEXT predicate is better suited for finding documents containing combinations of the search terms spread throughout the column, in contrast to the CONTAINS predicate, which is better suited for finding "exact" matches. You can also perform exact-phrase matching with the FREETEXT predicate by placing the phrase in double quotation marks.

 

This query returns you all paths to files which contain the "Hello World" text.

SELECT Path

 FROM SCOPE()
WHERE FREETEXT(Contents, 'Hello World')