How to do a LIKE considering two columns?

I have a customer table with two columns first_name and last_name.

How can I use LIKE in a query being able 开发者_如何学编程to get data from both columns at same time?

For instance:

SELECT CONCAT(first_name, ' ', last_name) as 'full_name' 
FROM customer WHERE full_name LIKE 'John D%'

I've tried this and it tells me full_name column doesn't exist.

SELECT CONCAT(first_name, ' ', last_name) as 'full_name' 
FROM customer WHERE CONCAT(first_name, ' ', last_name) LIKE 'John D%'

You are almost there

SELECT * 
FROM customer 
WHERE CONCAT(first_name, ' ', last_name) LIKE 'John D%'

Note: this may not have very good performance. You might want to consider full text search.

Use HAVING instead of WHERE:

SELECT CONCAT(first_name, ' ', last_name) as 'full_name' 
   FROM customer HAVING full_name LIKE 'John D%'

I don't have MySql at hand, but can't u do something like the code below?

SELECT CONCAT(first_name, ' ', last_name) as 'full_name' 
FROM customer WHERE CONCAT(first_name, ' ', last_name) LIKE 'John D%'

Disclaimer: THIS COULD BE VERY SLOW!!!

SELECT * 
FROM customer 
WHERE CONCAT(first_name, ' ', last_name) LIKE 'John D%' 

might be very slow but that won't matter if your database is quite small. When using CONCAT make sure the Collation is same for both the column names or else it will fetch you an error.

Below is a statement for when I want my LIKE statement to work for both category name and course name. I check for cat_id in both tables so that I can have an idea which course belongs to which category.

SELECT * FROM courses a INNER JOIN categories b ON a.cat_id=b.cat_id 
WHERE CONCAT(b.cat_name,' ',a.course_name)
LIKE :name ORDER BY b.cat_id

:name is placeholder (PDO)