Searching 2 Fields At Once

I have a table with columns firstName and lastName. I’d like to build an SQL statement that searches them both at the same time, something like:


... WHERE (firstName." ".lastName AS name) LIKE %Jeff Jones%

Obviously, I have no idea how the syntax goes. I know this is absurdly simple, but any help is appreciated.

My gut tells me that this should stick with searching individual columns so that indexes can be used.

WHERE lastName = 'Jones' AND firstName = 'Jeff'

rather than using a HAVING clause (which never made any sense to me if there’s no GROUP BY clause), i prefer to do this –

SELECT *
  FROM ( SELECT CONCAT_WS(" ", firstName, lastName) 
                 AS fullName
           FROM people ) AS t
 WHERE fullName LIKE "%Jeff Jones%"

this is, incidentally, one of those very rare cases where it’s okay to use the dreaded, evil “select star” (because the columns are explicitly stated in the subquery)

you can’t refer to an alias column in a WHERE clause you need to use a HAVING clause.

The database I’m looking to build will hold tables for companies and people, with notes about each. I want to have a single search field that will search the company name, the person’s full name, and those notes. But the names are stored in the table as first and last, hence the question.

When I get the search string, I’d rather not mess with it at all. Sometimes the string will be a single word, sometimes it will be more than two, etc. I won’t be able to parse the string for a “first name” and a “last name” that I can split up in the SQL query. Is there some way to make this work?

(Sorry that this wasn’t clear in the first post.)

After a little RTFM, here’s what I have:

[highlight=‘sql’]
SELECT CONCAT_WS(" ", firstName, lastName) AS fullName
FROM people
WHERE fullName LIKE “%Jeff Jones%”



But I'm getting an error: "Unknown column 'fullName' in 'where clause' "

Am I doing something wrong with the AS part?

you must have ~some~ idea, because you were fairly close…

WHERE CONCAT(firstName,lastName) LIKE '%Jeff%Jones%'

:slight_smile: