Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql - how to use like on multiple columns

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'%
like image 742
saverix80 Avatar asked Mar 10 '16 11:03

saverix80


People also ask

How do I use like on multiple columns?

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. 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.

How use LIKE operator in SQL for multiple values in MySQL?

The MySQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

How do I match two columns in MySQL?

Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.

Does MySQL use index for like?

MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character.


3 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.

like image 56
sagi Avatar answered Oct 21 '22 12:10

sagi


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.

like image 27
and1 Avatar answered Oct 21 '22 14:10

and1


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%')
)
like image 33
champ.exe Avatar answered Oct 21 '22 14:10

champ.exe