wulung_try wulung_try - 4 years ago 103
SQL Question

MySQL - Single criteria for multiple columns within single table - MySQL

I would appreciate any help creating this query. I will make a simple example of what I want to do. I have a table (named t1) that is structurally similar to the following:

id f_name l_name address
----------------------------------
1 alpha beta city phi
2 gamma beta city beta
3 alpha lambda city sigma
4 beta omega city beta


I want to find the data that contain predetermined criteria. These criteria apply to all columns in the table.

For example I want to find data that contains the word 'beta' on the table. I use this query before, but didn't work.

SELECT *
FROM t1
WHERE id LIKE '%criteria%'
OR f_name LIKE '%criteria%'
OR l_name LIKE '%criteria%'


I think I need to do a sub-query of some sort. But really don't know how to proceed, Thank you.

Answer Source

Since it does not matter for you, which column satisfies your criterion, the simplest way I can see is to concatenate all the columns like this:

SELECT *
FROM t1
WHERE CONCAT(id, f_name, l_name, .....) LIKE '%criteria%'

The above query will be equivalent to specifying many LIKE conditions with OR operator. Moreover, it will avoid errors in case your columns are of different datatypes, because CONCAT makes automatic conversion

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download