using mysql concat and searching for that field using like, does that work?

Question

I am creating a query like the following

SELECT CONCAT(u.firstName," ",u.lastName) as user

then later on in the query I write:

WHERE user LIKE '%jay%'

I am getting no results. Are you not able to use a concatenated field this way?

Answer 1

SELECT CONCAT(u.firstName," ",u.lastName) as user ... WHERE user LIKE '%jay%'

will not work, but

SELECT CONCAT(u.firstName," ",u.lastName) as user ... HAVING user LIKE '%jay%'

will work, as HAVING works on the results of the query, whereas WHERE does not.