Skip to content

Full Text Search – CONTAINS

September 27, 2012

I’ve been working on a new presentation for full text search and brushing up on some of my T-SQL operators. Part of my talk goes into the CONTAINS operator, which is one of the full text search keywords you need to know.

This operator is only used with full text indexes, so if you have a column that isn’t full-text indexed, it returns an error. If I issue this:

 FROM dbo.salary
  WHERE CONTAINS(empname, 'Steve')

I get this:

Msg 7601, Level 16, State 2, Line 3

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘dbo.salary’ because it is not full-text indexed.

I have a table that is full text indexed and I can issue a basic query, which looks like so many other T-SQL queries.

 FROM authordrafts
 WHERE CONTAINS(*, 'AlwaysOn')

This returns me all the rows where the columns in the full-text index (I used the star, *), have the term “AlwaysOn” in them. In this case, I’m hitting a FileTable table with lots of whitepapers in there.


This query is essentially a LIKE search, but it isn’t doing character matching. Instead it is working with those keywords in the full text index. I’ve used a simple search above. I could replace the * with the column, in this case the file_stream column.

 FROM authordrafts
 WHERE CONTAINS(file_stream, 'AlwaysOn')

I could also use a prefix term and the * wildcard, similar to LIKE.

 FROM authordrafts
 WHERE CONTAINS(file_stream, 'Always*')

These match other rows where “always” is the document, which matches “always” as a standalone word as well as “alwayson” as a term.

I could also limit the search to particular columns, using parenthesis and commas to separate them out. The BOL example from the CONTAINS page does a nice job of showing this.

Use AdventureWorks2012;
SELECT Name, Color
 FROM Production.Product
 WHERE CONTAINS((Name, Color), 'Red');

This is just a very basic look at CONTAINS. In another post, I’ll look at a few more possibilities with this term.

About these ads

From → Blog

  1. bhuvnesh permalink


    during my testing or learning on Full text search , i found below queries are returning different results .can you please guide me why

    select [BusinessEntityID] , [Name] from [Person].[Person_BK]
    where contains([Name], ‘*ASDGDE*’) or contains([Name], ‘*AFASFA*’)

    select [BusinessEntityID] , [Name] from [Person].[Person_BK]
    where [Name] like ‘%ASDGDE%’ or [Name] like ‘%AFASFA%’

  2. bhuvnesh permalink

    i got the correct query select * from [Person].[Person_BK] where
    contains([Name], ‘*ASDGDE* OR *AFASFA*’)

    • I’m not entirely sure what your differences or are what you are looking for. LIKE works differently than CONTAINS. If you post a more detailed question with what your results are at with your question, we’ll get you an answer.

Comments are closed.


Get every new post delivered to your Inbox.

Join 4,301 other followers

%d bloggers like this: