Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Full Text Search Querying Alternatives in SQL Server


By:   |   Read Comments (3)   |   Related Tips: 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



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, December 05, 2014 - 6:29:43 AM - ras lila Back To Top

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 Back To Top

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 Back To Top

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


Learn more about SQL Server tools