CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Marc
Marc

Reputation: 81

SQL WHERE statement multiple columns LIKE one value

Trying to get a SQL query where you define the value once and it searches multiple columns. I got this working for the equal operator, but I need this for a LIKE operator as well. This is were the problem is.

SELECT *
FROM table1
INNER JOIN table2 ON table2.id = table1.ref_id
WHERE (table1.email, table2.email) LIKE '%[email protected]%'

This is my current query which is not working. Looked at a few other questions online, but those solutions didn't work for me.

How can I search multiple columns for one value with a LIKE operator?

Upvotes: 1

Views: 1060

Answers (2)

user2314737
user2314737

Reputation: 29387

Use

SELECT *
FROM table1
INNER JOIN table2 ON table2.id = table1.ref_id
WHERE 
table1.email LIKE '%[email protected]%'
OR
table2.email LIKE '%[email protected]%'

This should be faster in general than other solutions using concatenations of different columns (with +, concat, CONCAT_WS, etc.) because of the overhead of running the concatenation for each row.

See also: Query performance with concatenation and LIKE.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270411

The specific answer to your question is something like this:

WHERE CONCAT_WS('|', table1.email, table2.email) LIKE '%[email protected]%'

That would generally not be used. The more common approach is simply:

WHERE table1.email LIKE '%[email protected]%' OR
      table2.email LIKE '%[email protected]%'

Upvotes: 3

Related Questions