sql - Search a whole table in mySQL for a string

Question

I'm trying to search a whole table in mySQL for a string.

I want to search all fields and all entrees of a table, returning each full entry that contains the specified text.

I can't figure out how to search multiple fields easily; here are the details:

The table is "clients". It has about 30 fields and 800 entries, too much to show all at once in a browser. I would like to search for a name (i.e. "Mary"), but it could be in the shipping_name field or the billing_name field, or the email field, etc.

I would like to search all fields for any entries that contain the string "Mary". This is what I think should work but doesn't:

SELECT * FROM `clients` IN 'Mary'

Answer 1

Try something like this:

SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"

You may want to see SQL docs for additional information on string operators and regular expressions.

Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '') instead of just field_i

Case sensitivity: You can use case insensitive collation or something like this:

... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")

Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECT operator syntax (WHERE section, to be precise).