locked
Mssql contains clause RRS feed

  • Question

  • I am using the query for searching the details like City,State,firstname,lastname etc., For searching i am using any one name above i have mentioned.,

    Now i need to use contains clause for every searching name and that will be in single query.

    My code where clause is:

    where contains(B.firstname,@firstname)
    and contains(B.lastname,@lastname)
    and contains(B.City,@City)
    and contains(B.State,@state)

    But it has produce syntax error. I need your help as soon as possible.

    Thanks in Advance

    Monday, October 24, 2011 11:04 AM

Answers

  • While Rick may be right that a direct string search would meet your needs, there is no problem with your syntax as such.  You can very easily AND together several CONTAINS statements.

    However, you did not tell us what the syntax error is, so all is guesswork.  However, if this is your error:

    Msg 7601, Level 16, State 3, Line 5
    Cannot use a CONTAINS or FREETEXT predicate on column 'ContentOther' because it is not full-text indexed.

    This means that you have to use the proper command to create the full text indexes, such as show below:

    CREATE TABLE dbo.Pictures
     (ID INT NOT NULL,
      ContentDetail NVARCHAR(100), 
      ContentOther NVARCHAR(100));
    
    CREATE UNIQUE INDEX ui_id ON dbo.Pictures(ID);
    CREATE FULLTEXT CATALOG ft AS DEFAULT;
    CREATE FULLTEXT INDEX ON dbo.Pictures
               (ContentDetail, ContentOther) 
               KEY INDEX ui_id;
    

    If you have done this, the you can use all of the columns that are full text indexed in your query.

    FWIW,
    RLF

    • Proposed as answer by Warwick Rudd Wednesday, October 26, 2011 12:59 AM
    • Marked as answer by Peja Tao Monday, October 31, 2011 1:44 AM
    Monday, October 24, 2011 8:58 PM
  • CONTAINS is used for searching a full-text index and is usually used for a fuzzy search, such as finding run, running, and ran in the same field. Since you are searching names, it seems unlikely that you want or need this full-text search feature.

    I suspect you should use = instead. As in

    WHERE B.firstname = @firstname
    AND B.lastname = @lastname
    AND B.City = @City
    AND B.State = @state


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Wednesday, October 26, 2011 5:35 AM
    • Marked as answer by Peja Tao Monday, October 31, 2011 1:44 AM
    Monday, October 24, 2011 3:28 PM

All replies

  • CONTAINS is used for searching a full-text index and is usually used for a fuzzy search, such as finding run, running, and ran in the same field. Since you are searching names, it seems unlikely that you want or need this full-text search feature.

    I suspect you should use = instead. As in

    WHERE B.firstname = @firstname
    AND B.lastname = @lastname
    AND B.City = @City
    AND B.State = @state


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Proposed as answer by Peja Tao Wednesday, October 26, 2011 5:35 AM
    • Marked as answer by Peja Tao Monday, October 31, 2011 1:44 AM
    Monday, October 24, 2011 3:28 PM
  • While Rick may be right that a direct string search would meet your needs, there is no problem with your syntax as such.  You can very easily AND together several CONTAINS statements.

    However, you did not tell us what the syntax error is, so all is guesswork.  However, if this is your error:

    Msg 7601, Level 16, State 3, Line 5
    Cannot use a CONTAINS or FREETEXT predicate on column 'ContentOther' because it is not full-text indexed.

    This means that you have to use the proper command to create the full text indexes, such as show below:

    CREATE TABLE dbo.Pictures
     (ID INT NOT NULL,
      ContentDetail NVARCHAR(100), 
      ContentOther NVARCHAR(100));
    
    CREATE UNIQUE INDEX ui_id ON dbo.Pictures(ID);
    CREATE FULLTEXT CATALOG ft AS DEFAULT;
    CREATE FULLTEXT INDEX ON dbo.Pictures
               (ContentDetail, ContentOther) 
               KEY INDEX ui_id;
    

    If you have done this, the you can use all of the columns that are full text indexed in your query.

    FWIW,
    RLF

    • Proposed as answer by Warwick Rudd Wednesday, October 26, 2011 12:59 AM
    • Marked as answer by Peja Tao Monday, October 31, 2011 1:44 AM
    Monday, October 24, 2011 8:58 PM