mysql - Combine two columns in SQL for WHERE clause

Question

In my SQL, I am using the WHERE and LIKE clauses to perform a search. However, I need to perform the search on a combined value of two columns - first_name and last_name:

WHERE customers.first_name + customers.last_name LIKE '%John Smith%'

This doesn't work, but I wondered how I could do something along these lines?

I have tried to do seperate the search by the two columns, like so:

WHERE customers.first_name LIKE '%John Smith%' OR customers.last_name LIKE '%John Smith%'

But obviously that will not work, because the search query is the combined value of these two columns.

Answer 1

Use the following:

WHERE CONCAT(customers.first_name, ' ', customers.last_name) LIKE '%John Smith%'

Note that in order for this to work as intended, first name and last name should be trimmed, i.e. they should not contain leading or trailing whitespaces. It's better to trim strings in PHP, before inserting to the database. But you can also incorporate trimming into your query like this:

WHERE CONCAT(TRIM(customers.first_name), ' ', TRIM(customers.last_name)) LIKE '%John Smith%'