MySQL select with CONCAT condition

Question

I'm trying to compile this in my mind.. i have a table with firstname and lastname fields and i have a string like "Bob Jones" or "Bob Michael Jones" and several others.

the thing is, i have for example Bob in firstname, and Michael Jones in lastname

so i'm trying to

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
  FROM users 
 WHERE firstlast = "Bob Michael Jones"

but it says unknown column "firstlast".. can anyone help please ?

score 183 · Accepted Answer · answered Apr 20 '11 at 18:02

183

The aliases you give are for the output of the query - they are not available within the query itself.

You can either repeat the expression:

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

or wrap the query

SELECT * FROM (
  SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
  FROM users) base 
WHERE firstLast = "Bob Michael Jones"

answered Apr 20 '11 at 18:02

mdma

56,943
12
94
128

5

this was to set as answer. – Arun Killu Jan 16 '13 at 09:35
after a while, i can say that i agree to use this as a better answer – Alex K May 14 '13 at 13:01
@Alex you can select a different answer if you wish to do so – gypaetus Aug 08 '13 at 19:32
1

For a bulky table with many rows, I think it would be unwise to use the "wrap the query" version. – Fandi Susanto Dec 14 '13 at 04:08

score 34 · Answer 2 · answered Apr 20 '11 at 18:00

34

Try this:

SELECT * 
  FROM  (
        SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
        FROM users 
    ) a
WHERE firstlast = "Bob Michael Jones"

answered Apr 20 '11 at 18:00

Chandu

81,493
19
133
134

works perfect for me, thanks a lot :) and thanks for putting text in code, i forgot – Alex K Apr 20 '11 at 18:02

score 10 · Answer 3 · answered Apr 20 '11 at 17:59

10

SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast 
FROM users 
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

answered Apr 20 '11 at 17:59

Jeff Swensen

3,513
28
52

Viraj Dhamal · Answer 4 · 2014-07-22T06:22:07.973

8

Use CONCAT_WS().

SELECT CONCAT_WS(' ',firstname,lastname) as firstlast FROM users 
WHERE firstlast = "Bob Michael Jones";

The first argument is the separator for the rest of the arguments.

edited Jul 22 '14 at 06:22

answered Jan 20 '14 at 10:37

Viraj Dhamal

5,165
10
32
41

so then it should be `CONCAT_WS(' ', ..` – Alex K Jan 30 '14 at 10:50

score 7 · Answer 5 · answered Jan 10 '14 at 14:32

7

There is an alternative to repeating the CONCAT expression or using subqueries. You can make use of the HAVING clause, which recognizes column aliases.

SELECT 
  neededfield, CONCAT(firstname, ' ', lastname) AS firstlast 
FROM
  users 
HAVING firstlast = "Bob Michael Jones"

Here is a working SQL Fiddle.

answered Jan 10 '14 at 14:32

Bogdan

43,166
12
128
129

Not sure why the having clause is not garnering more attention. It allows the direct use of the virtual column name. Does the having clause have more overhead? – Paul Mar 18 '14 at 17:11
@Paul having clause applied in the end of executing query so we can use it to set condition on aggregate functions(like MAX()). Having clause cannot use index so it is slow. – Mostafa Vatanpour Jul 05 '16 at 04:15

score 7 · Answer 6 · answered Apr 20 '11 at 18:00

Try:

SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast 
  FROM users 
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"

Your alias firstlast is not available in the where clause of the query unless you do the query as a sub-select.

MySQL select with CONCAT condition

6 Answers6

Linked