SQL WHERE statement multiple columns LIKE one value

Question

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?

Charlatan

Are you doing something with parameters that only allows the pattern to appear once in the query? – Bellyache

Answer 1

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]%'

Kliman

Answer 2

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.

Brahe