How to do a LIKE considering two columns?

Question

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.

[Full Text Search](http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) is the most efficient means in SQL to do what you want. — OMG Ponies, Aug 11 '11 at 14:15
@OMG As I could change the table and the data insertion, I created a new indexed column full_name and used it instead due to performance issues mentioned in the answers. — RedDragon, Aug 11 '11 at 15:20

score 36 · Accepted Answer · answered Aug 11 '11 at 14:15

36

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

answered Aug 11 '11 at 14:15

Ned Batchelder

364,293
75
561
662

1

You might also want to trim the first_name and last_name to account for possible extra spaces in the columns. – Narnian Aug 11 '11 at 14:17
1

You also might check a "lastname, firstname" variant if the user can input that as well. – Narnian Aug 11 '11 at 14:17
So Simple, brilliant. danke :) – arjunaaji Jun 13 '14 at 06:42
1

'CONCAT' is not a recognized built-in function name. – Jitendra Pancholi Mar 20 '15 at 13:28
2

What is wrong with using 'full_name' in where clause? – robsch Oct 13 '17 at 10:38

score 6 · Answer 2 · answered Aug 11 '11 at 14:15

6

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.

answered Aug 11 '11 at 14:15

Nivas

18,126
4
62
76

score 4 · Answer 3 · answered Aug 11 '11 at 14:20

4

Use HAVING instead of WHERE:

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

answered Aug 11 '11 at 14:20

nobody

10,599
4
26
43

1

Why HAVING instead of WHERE? – RN Kushwaha Dec 30 '15 at 06:20

score 0 · Answer 4 · edited Sep 03 '13 at 19:55

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)

score 0 · Answer 5 · answered Aug 11 '11 at 14:18

0

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

answered Aug 11 '11 at 14:18

Kralizek

1,999
1
28
47

How to do a LIKE considering two columns?

5 Answers5