locked
CONTAINS - RC0 2012 RRS feed

  • Question

  • I am trying to use the new CONTAINS tool in sql2012 RC0. I set up the full text search. But when I run a query my results show only records with an exact match. Any ideas?
    Monday, January 23, 2012 2:45 PM

All replies

  • CONTAINS has been in the language since SQL 7...

    Can you give a more concrete demonstration of your problem? How does your query look like? What data do you get back? What other data did you expect to get back?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 23, 2012 11:03 PM
  • SELECT c_column
    FROM T_table
    WHERE CONTAINS(c_column,'performance');
    returns the columns containing 'performance' but no other values. For example, 'performances' , which would be the simplest form of similarity does not show in the result set. Likewise, if I query 'performances' the singular 'performance' does not show.
    In both cases I expect both forms of the word to show in the resultset.
    Thanks.
    Tuesday, January 24, 2012 5:39 AM
  • You need to use FORMSOF, see script below.

    If you want to learn fulltext, I recommend this book,
    http://www.sqlservermvpdeepdives.com/
    Chapter 13 by Robert Cain gives an excellent introduction to full-text. And, no, I am not ashamed plugging that book; all our royalties goes to War Child International.

    CREATE DATABASE sobertext
    go
    USE sobertext
    go
    CREATE SEQUENCE seq AS int START WITH 1
    go
    CREATE TABLE mytexttable
       (a int NOT NULL DEFAULT NEXT VALUE FOR seq,
        mytext nvarchar(MAX) NOT NULL,
        CONSTRAINT pk PRIMARY KEY(a)
    )
    INSERT mytexttable(mytext)
       VALUES ('This is a question of performance, or at least we think so'),
              ('There are several performances in our town this week.'),
              ('They are coming to more towns next week.')
    go
    CREATE FULLTEXT CATALOG katta AS DEFAULT
    go
    CREATE FULLTEXT INDEX ON mytexttable (mytext) KEY INDEX pk
    go
    WAITFOR DELAY '00:00:10'
    go
    SELECT * FROM mytexttable WHERE CONTAINS(mytext, 'FORMSOF (INFLECTIONAL, town)')
    SELECT * FROM mytexttable WHERE CONTAINS(mytext, 'FORMSOF (INFLECTIONAL, performance)')
    go
    USE tempdb
    go
    DROP DATABASE sobertext


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 24, 2012 10:04 PM
  • I copy/pasted your code and ran it separately  in 2008 and 2012.

    My results showed the exact matches only. The similar matches were not included.

    Suggestions? Thanks.

    Thursday, February 2, 2012 12:57 PM
  • I would assume that your default language is not English.

    Try changing the definition of the fulltext language to be:

    CREATE FULLTEXT INDEX ON mytexttable (mytext LANGUAGE 1033) KEY INDEX pk

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 2, 2012 10:25 PM
  • That worked. I was thinking along those lines and it's always nice to have my thoughts confirmed by an expert. Thank you very much.
    Sunday, February 19, 2012 11:26 AM
  • Also, since use of fulltext serach is new to me I didn't know how to convert the idea to code.
    Sunday, February 19, 2012 11:27 AM