CONCAT() function in MySQL is used to concatenating the given arguments. It may have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string.
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"
Try this:
SELECT *
FROM (
SELECT neededfield, CONCAT(firstname, ' ', lastname) as firstlast
FROM users
) a
WHERE firstlast = "Bob Michael Jones"
SELECT needefield, CONCAT(firstname, ' ',lastname) as firstlast
FROM users
WHERE CONCAT(firstname, ' ', lastname) = "Bob Michael Jones"
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With