1

I am trying to upgrade the search on my website.

I have stumbled across CONTAINS.

I have set the columns that I want to search to FULLTEXT.

What am I missing?

title = Test is what i'm doing

SELECT * FROM test WHERE title LIKE "%Test%"

Works Fine.

SELECT * FROM test WHERE CONTAINS('*', "'Test*'")

Returns nothing.

I have tried

SELECT * FROM test WHERE CONTAINS(title, "Test")

and everything else I could think of.

Everything with Contains returns nothing.

Here is my table structure.

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  ID  15642   A           
 Edit    Drop   title   FULLTEXT    No  No  title   1       YES

What am I missing here?

I am using MySQL 5.5

Steve Payne
  • 612
  • 3
  • 8
  • 16
  • Since functionality varies, it would help if you identified what DBMS you're using. MySQL <> SQL Server <> Oracle <> DB2 etc. Your question is similar to asking "How do I say "Where's the bathroom?" without specifying what language you need to say it in - if I tell you in English and you're in France and no one understands you, you're in trouble. :-) – Ken White Aug 16 '12 at 02:06

2 Answers2

0

CONTAINS only works when you have a full text indexed column. Presumably, that is not in place.

Follow the documentation at http://msdn.microsoft.com/en-us/library/ms142497 to help set one up.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I thought I do have it set to FULLTEXT. I am looking at this documentation now, little confused on it all. Maybe I should create a dummy table and see what I can do. I hesitate to do a huge change to something and mess it up. – Steve Payne Aug 16 '12 at 02:47
  • This answer should have been delayed until the DBMS was provided. SQL Server specific answers only apply when the question relates to SQL Server. – Ken White Aug 16 '12 at 02:49
0

  1. The title column must be indexed for CONTAINS to work. Is the title column indexed?

  2. According to this post, there are limitations on the use of wildcards with the contains keyword.

  3. Depending on your DB implementation a double quote may cause unexpected results. Change this to a single quote to be safe. (Some of the examples at MSDN use double quotes and single quotes, but the outer quotes are single quotes (different from you example).

  4. Are you certain there are records in your db that have the specified string "Test"?

  5. According to one of the replies at http://forums.asp.net/t/1639440.aspx , the CONTAINS operator is unavailable in SQL Express. You didn't mention which DB you are using, so this may or may not apply.

Community
  • 1
  • 1
smartcaveman
  • 41,281
  • 29
  • 127
  • 212
  • What do you mean by indexed? I am using webmin to create the FULLTEXT index. I am clicking, Create an index on 1 columns. And then setting the field to FULLTEXT, so I assume that is an "index"? – Steve Payne Aug 16 '12 at 02:56