MySQL match a value with wildcard in multiple columns

I have a teachers table that looks like this:

teacherid	teacherfname	teacherlname	salary
1	Alexander	Bennett	55.30

I would like to return any record that contains a given string in the teacherfname, teacherlname and salary columns.

What I have right now (this returns exact match only):

SELECT * FROM `teachers` WHERE 'Alexander' IN (teacherfname, teacherlname, salary)

What I would like to do is something like this (this would not return anything):

SELECT * FROM `teachers` WHERE '%Alex%' IN (teacherfname, teacherlname, salary)

What do I need to make the query work? Thank you.

Answer

I would assume that the value %Alex% won’t ever match the salary column. If you want to search for any rows where the first name or last name include “Alex” I would use simple pattern matching, and force all comparisons to use the same letter case.

For example:

SELECT * 
FROM `teachers`
WHERE lower(teacherfname) like '%alex%' 
   or lower(teacherlname) like '%alex%'