CODEWITHSUNDEEP
mysqlsqlconcatenationsql-like
RedDragon
RedDragon

Reputation: 2138

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.

Upvotes: 20

Views: 17158

Answers (5)

user2634882
user2634882

Reputation: 179

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)

Upvotes: 0

nobody
nobody

Reputation: 10645

Use HAVING instead of WHERE:

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

Upvotes: 4

Kralizek
Kralizek

Reputation: 2063

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!!!

Upvotes: 0

Ned Batchelder
Ned Batchelder

Reputation: 375624

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

Upvotes: 36

Nivas
Nivas

Reputation: 18334

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.

Upvotes: 6

Related Questions