Mysql - how to use like on multiple columns

Question

I've searched in mysql query a word in multiple column in java program. The number of column is variable. It is correct this query:

select * from customer with (city, name) like%'adelaide'%

Answer 1

You can use CONCAT() function:

select * from customer WHERE concat(city,name) like '%adelaide%'

You can add as many columns to the concat function as you like. Also as you see I changed your like syntax, '%WORD%' and used a simple where clause.

Answer 2

It's better to use CONCAT_WS() function.

CONCAT() returns NULL if any argument is NULL.

CONCAT_WS() skip any NULL values after the separator argument.

Answer 3

multiple like statements can not be used with or directly. You have to use column name for each like statement.

Use multiple like as mentioned below.

Select * 
from animals 
where 
(
[animalscolumn] like ('%dog%') or 
[animalscolumn] like ('%cat%') or 
[animalscolumn] like ('%gerbil%') or 
[animalscolumn] like ('%hamster%') or 
[animalscolumn] like ('%guinea pig%')
)