How do you use T-SQL Full-Text Search to get results like Google?

Full-Text Search run against character-based data in SQL Server tables. These queries can include words or phrase searching. Before we can run full-text queries on a table, first we need to create a full-text index on the table. Only one full-text index is allowed per table and this index can contain up to 1024 columns.

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, char, nvarchar, text, ntext, image, xml, or varbinary.

Full text queries perform searches against text data, in full-text indexes by operating on words and phrases based on rules of a particular language.

To implement full-text indexing in SQL Server, you should take the following steps:

1. Create a full-text catalog, if necessary.
2. Create the full-text index.
3. Modify the list of noise words (SQL Server 2005) or stop words (SQL Server 2008), if necessary.
4. Modify the thesaurus for the language being used, if necessary.

Step 1: Check Search Query with like keyword

                                                    Check output of like query

Step 2: Create Full Text Catalog

                                       Now provide a name to full text catalog.

new catalog has been created in Storage folder.

Step 3: Create Full Text Index

Select one Unique Index. “Full Text Index” table must have at least one unique index.

Select columns name and language types for columns.

Select change tracking.

Now select the full-text catalog for index.

Step 3: Populate the Index

FREETEXT :

FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string. Check here like and Free-text search output.

CONTAINS:

Searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.