0

Possible Duplicates:
MYSQL query for searching through ALL the fields?
MySQL - Search in all fields from every table from a database

I have 28 fields in a table and one option I would like to give a user is to search every entry across all its fields. So Instead of writing something like this:

SELECT * FROM table WHERE field1 LIKE testfield OR field2 LIKE testfield OR field3 LIKE testfield 
OR field4 LIKE testfield OR  ... etc

I would like to write something like this:

SELECT * FROM table WHERE * LIKE testfield;

This doesnt work so I wondering if there is another syntax or the feature doesnt exist?

Community
  • 1
  • 1
twerdster
  • 4,977
  • 3
  • 40
  • 70
  • 1
    same question http://stackoverflow.com/questions/3797906/mysql-query-for-searching-through-all-the-fields that links to another similar question... – manji Apr 03 '11 at 20:41
  • 1
    http://stackoverflow.com/questions/639531/mysql-search-in-all-fields-from-every-table-from-a-database – Spyros Apr 03 '11 at 20:41
  • The question SpyrosP has linked to has the better answers of the two. – Tom Anderson Apr 03 '11 at 20:49

1 Answers1

0

There's no portable support for this type of query in SQL. Your best bet is to create another string/varchar column and concat the other columns into a string and then search on that.

SQL Server does support a contains keyword:

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

But, if you want to be portable, you're better off with the extra column.

edoloughlin
  • 5,821
  • 4
  • 32
  • 61