Full Text Search Querying Alternatives in SQL Server

By: Jeremy Kadlec | Comments (4) | Related: More > Full Text Search

Problem

In one of your recent tips, you outlined setting up a SQL Server 2005 Full Text Catalog and some basic queries. Can you outline some of the Full Text Search querying alternatives? I thought your first tip (Making the case for Full Text Search) was beneficial to get started, now I think I want to take the next step with Full Text Search to see if it can functionally meet some application needs that we have been struggling with for some time.

Solution

Well let's just dive into examples a few different SQL Server 2005 examples with the AdventureWorks sample database to see if these will meet your needs. Although Full Text Search has 4 common commands to access the Full Text Catalogs, let's try to dive into a number of different options with the CONTAINS command and see if we can cover many of the variations.

CONTAINS - Syntax Options

CONTAINS
          ( { column_name | (column_list) | * } 
               , '< contains_search_condition >'     
                    [ , LANGUAGE language_term ]
        ) 
< contains_search_condition > ::= 
    { < simple_term > 
    | < prefix_term > 
    | < generation_term > 
    | < proximity_term > 
    | < weighted_term > 
    } 
    | { ( < contains_search_condition > ) 
    [ { < AND > | < AND NOT > | < OR > } ] 
    < contains_search_condition > [ ...n ] 
    } 
< simple_term > ::= 
          word | " phrase "
< prefix term > ::= 
     { "word * " | "phrase *" }
< generation_term > ::= 
     FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] ) 
< proximity_term > ::= 
     { < simple_term > | < prefix_term > } 
     { { NEAR | ~ }
     { < simple_term > | < prefix_term > } 
     } [ ...n ] 
< weighted_term > ::= 
     ISABOUT 
        ( { { 
  < simple_term > 
  | < prefix_term > 
  | < generation_term > 
  | < proximity_term > 
  } 
   [ WEIGHT ( weight_value ) ] 
   } [ ,...n ] 
        ) 
< AND > ::= 
     { AND | & }
< AND NOT > ::= 
     { AND NOT | & !}
< OR > ::= 
     { OR | | }

Source - SQL Server 2005 Books Online - CONTAINS

CONTAINS - OR Logic

In this query the terms 'nut', 'screw' or 'washer' are returned. As a note, the '*' in the first parameter of the CONTAINS statement indicates that all columns in the table registered with Full Text Search should be searched in this query.

USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE CONTAINS(*, '"*nut*" OR "*screw*" OR "*washer*"');
GO

CONTAINS - AND Logic

In the first example query just the Name column is queried for the terms 'flat' and 'washer'. In the second example, the term 'nut' is searched without 'hex', returning results such as 'chainring nuts', 'lock nuts', etc. These are both examples of two independent terms being included in the search criteria.

-- AND Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat" AND "washer"');
GO

-- AND NOT Logic
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"nut" AND NOT "hex"');
GO

In this example, the only difference is that the phrase 'flat washer' is queried as opposed to two separate terms.

USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"flat washer"');
GO

As a note for these two specific queries with the original data set, these two queries return the same results, but with a different data set, that may not be the case because the two independent terms could be more prevalent than the single phrase (combination of both terms).

CONTAINS - Prefix

In this example, the prefix 'chain' is queried which returns the word 'chain', but also matches on a single word like 'chainring' and the phrase 'chainring bolts'.

USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], '"chain*"');
GO

CONTAINS - Proximity

In the first example, the NEAR keyword is used to find the word 'men' near 'shorts'. In the second example, three words are used. As a point of reference, the order of the words does not seem to generate different result sets.

-- 2 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'men NEAR shorts');
GO

-- 3 terms
USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'XL NEAR men NEAR shorts');
GO

CONTAINS - Inflection and Thesaurus

The first example is inflectional which means that the various forms of 'shift' will be returned, such as 'shifted', 'shifting', etc. The second example is based on the SQL Server Thesaurus setup in the SQL_Server_install_path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\ directory. These are a set of XML files that can be customized with synonyms for terms specific to your application.

-- INFLECTIONAL
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (INFLECTIONAL, shift) ');
GO

-- THESAURUS
USE AdventureWorks;
GO
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, ' FORMSOF (THESAURUS, wash) ');
GO

CONTAINS - Weight

In this example, the weight of the terms 'nut', 'bolt' and 'washer' are selected based on the weight in that order.

USE AdventureWorks;
GO
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], 'ISABOUT (nut weight (.8),
bolt weight (.4), washer weight (.2) )' );
GO

CONTAINS - Variable

In the two examples below, variable strings are passed into the CONTAINS command. Based on my testing, as long as the strings have the proper syntax then just about anything can be passed with the variable, which makes the programming a little easier from front end search interfaces.

-- Example 1
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = 'XL NEAR men NEAR shorts'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO

-- Example 2
USE AdventureWorks;
GO
DECLARE @Parm1 varchar(50)
SET @Parm1 = '"XL" OR "men" OR "shorts"'
SELECT ProductID, [Name]
FROM Production.Product
WHERE CONTAINS([Name], @Parm1);
GO

Next Steps

As indicated in the earlier tip (Making the case for Full Text Search), Full Text Search may be a reasonable alternative to issuing some other T-SQL commands for complex queries, so the next time you are faced with a complex query scenario consider Full Text Search as a viable alternative.
For more information about some of these techniques, reference the following URLs:
- CONTAINS
For more information about Full Text Search on MSSQLTips.com, check out these URLs:
Stay tuned for additional tips outlining the capabilities of the other Full Text Search commands.
If you have additional samples that you would like to share with the community, feel free to forward them to [email protected] and we will update this tip with your post and give you credit for contribution.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.

View all my tips

Comments For This Article

Sunday, August 27, 2017 - 3:45:41 PM - Mehmet

İ have a problem with contains function, when i search with like '%ZAM%' operator, it finds all word that contains ZAM like ZAMANLAMA AZAMI ZAM and etc.. but when I use fts index contains function, it just find ZAM ZAMANLAMA but it doesnt find AZAMI or 123ZAM789. I have also tried CONTAINS (YourColumn, ' "ZAM" ' ) but it doesn't work. Please help me , fts is very fast but it could not find all contains like '%%' operator what should I do ?

Friday, December 5, 2014 - 6:29:43 AM - ras lila

DECLARE @Parm1 varchar(50)
SET @Parm1 ='"XL" OR "men" OR "shorts"'
SELECT ProductID, [Name]
FROM Production.Product
WHERECONTAINS([Name], @Parm1);
GO

Monday, May 19, 2014 - 3:59:29 PM - Chris

In the first example, the first "*" in the phrase "*nut*" is not necessary, and, in fact, does not work with full-text indexing, right?!

Thanks...Chris

Friday, March 14, 2014 - 5:22:07 AM - venkat

We need find the 5 digit string based on given condins

Condition1. First 3 characters should be 486
Condition2. 4 charater may be 4 or 2
Condition3. Fifth character my be 1 to 9

For above condition sample out puts or 48645, 48623....

I wrote the query for above scenario using like operator as given

select col1 from table1 where col1 like '486[42][1-9]'

I want to write the query for the same using full text search contains operator.

If you have any idea on this please mail it to me.

Regards,
Venkat