Full Text Searching  «Prev  Next»
Lesson 5 Implementing full-text searching
Objective Practice implementing full-text searching.

Implementing full-text Searching in SQL-Server

Once you have created your full-text indexes, you are ready to start using them in your queries. There are some special commands built into Transact-SQL that are specifically used in full-text searching. These are the special commands:
COMMAND ACTION
CONTAINS Allows you to search for specific words or phrases within the column(s) defined in the full-text index
FREETEXT Searches for multiple, less precise words, phrases, or sentences. The full-text search engine extracts the important words out of the words.
CONTAINSTABLE Specified in the FROM clause of a query, used as a table name that contains specific words and phrases. The CONTAINSTABLE command returns two columns of values, Key and Rank. Key is the value for the key column in the index. This was specified when the index was created. Rank is the ranking of the search results.
FREETEXTTABLE Specified in the FROM clause of a query, used as a table name that contains less precise words, phrases, or sentences. The FREETEXTTABLE command returns two columns of values, Key and Rank. Key is the value for the key column in the index. This was specified when the index was created. Rank is the ranking of the search results.

Let us look at some examples of the various commands in the SlideShow below. In each situation, the value in the Notes text column in the Employees table for EmployeeID 101 will be the following:


Free Text Query

1) Notice the wildcards in the query above. This indicates that trouble can appear anywhere in the text. This is very similar to a LIKE clause. The exception is that you can specify conditions within the text, such as AND and OR.
1) Notice the wildcards in the query above. This indicates that trouble can appear anywhere in the text. This is very similar to a LIKE clause. The exception is that you can specify conditions within the text, such as AND and OR.

2) To construct a free-text query using FREETEXT, you could issue a statement similar to the code above.
2) To construct a free-text query using FREETEXT, you could issue a statement similar to the code above. 
SELECT *
FROM employees 
WHERE FREETEXT (Notes, "relocated to other departments")

3) If you want to use a CONTAINS query and join the results to another table, you must use the CONTAINSTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a CONTAINS query.
3) If you want to use a CONTAINS query and join the results to another table, you must use the CONTAINSTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a CONTAINS query. 
SELECT E.LastName, E.FirstName
FROM Employees
AS E JOIN CONTAINSTABLE (Employees, Notes, '*trouble*') AS FT
ON E.EmployeeID = FT.[KEY]

4) If you want use a FREETEXT query and join the results to another table, you must use the FREETEXTTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a FREETEXT query.
4) If you want use a FREETEXT query and join the results to another table, you must use the FREETEXTTABLE command. Because it returns only a Key column and Rank column, you can join the Key column with the column that represents the key in the table. This example uses the EmployeeID column as the key and returns the LastName and FirstName column for a FREETEXT query. 
SELECT E.LastName, E.FirstName
FROM Employees
AS E JOIN FREETEXTTABLE (Employees, Notes, 'relocated to other departments') AS FT
ON E.EmployeeID = FT.[KEY]

In the next lesson, we will review the information covered in this module.

Implement FullText Searching - Exercise

Click the Exercise link below on the left to practice creating a free-text query.
Implement FullText Searching - Exercise

SEMrush Software