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'%
-
1Does this answer your question? Checking if a string is found in one of multiple columns in mySQLmickmackusa– mickmackusa ♦2021-09-13 22:56:37 +00:00Commented Sep 13, 2021 at 22:56
Add a comment
|
4 Answers
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.
2 Comments
cl0ne
For this to work correctly you have to be sure that there no case when columns don't contain "adelaide" in them but the concatenated string does. E.g. for column1 = 'Paradela' and column2 = 'idea'.
sagi
You're right, but it can be solved by tweeking it, for example concatenating a unique character between the two columns @cl0ne
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.
Comments
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%')
)
1 Comment
Gianluca Ghettini
much better using CONCAT. Using OR will not work when the search query spans multiple columns
select * from customer with city like%'adelaide'% or name like%'adelaide'%
1 Comment
executable
Can you please explain why it answer to the question ?