SQL Server, how to implement FULL TEXT search.

One of the most useful function that I like in SQL Server is the FULL TEXT search. When you have a table or some tables with string fields, like VARCHAR or NVARCHAR, you know that running LIKE clause is CPU consuming.

In order to run this example you must before activate the FULL TEXT search capability in your SQL engine. To do that:

  1. Open the SQL Server surface area configuration.
  2. Open the Services and Connections page.
  3. Configure the service to be enable.

Now you must run this Stored Procedure in order to activate the service in the Management Studio:

   1: EXEC sp_fulltext_database 'enable'


Last step is to activate the service for the tables, fields you want to use.

  1. Right click on the table and choose: Full text index>>define new …
  2. Define the columns that will take part in the index, the schedule and the file group (consider a separated file group for big data)

Now we can run our examples. We will use the two clause for implementing a FULL TEXT search, the CONTAINS and the FREETEXT.

Please refer to the previous post for the table schema ().

Using the CONTAINS clause.

The CONTAINS function search the exact word matches and word prefix matches. Let’s go to search all the addresses with the word Street.

   1: SELECT 
   2:     E.FirstName, E.LastName, A.Street
   3: FROM 
   4:     Employee AS E INNER JOIN Address AS A
   5: ON
   6:     E.Employee_id = A.Employee_fk
   7: WHERE CONTAINS(A.Street, 'Street')

And this is our result:

   1: Carl    Mark            Main Street
   2: Carl    Mark            Front Street
   3: Pier    Francoise       Red Street
   4: Mary    Stevenson       White main street
 

In this case with the CONTAINS clause we will get all the rows where there is a word Street but not the rows where the word Street  is inside another word like: MainStreet. To do that we need this syntax:

   1: WHERE CONTAINS(A.Street, '"*street"')

If you note, you need a double quote and a * before to find everything with street at the end.

Using the FREETEXT clause to get more results.

If you need to match all the result with the street word inside and similar results, you need to use the FREETEXT clause.

With the freetext clause, SQL generates various forms of the search term, breaking single words into parts.

   1: SELECT 
   2:     E.FirstName, E.LastName, A.Street
   3: FROM 
   4:     Employee AS E INNER JOIN Address AS A
   5: ON
   6:     E.Employee_id = A.Employee_fk
   7: WHERE FREETEXT(A.Street, 'street')


The result here will be:

   1: Carl    Mark         Main Street
   2: Carl    Mark         Front Street
   3: Pier    Francoise    Red Street
   4: Pier    Francoise    mainstreet