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

User · Answer

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

User · Answer

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

User · Answer

SELECT needefield  CONCAT firstname      lastname  as firstlast  FROM users  WHERE CONCAT firstname       lastname     Bob Michael Jones

User · Answer

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

User · Answer

Try this   SELECT      FROM            SELECT neededfield  CONCAT firstname       lastname  as firstlast          FROM users        a WHERE firstlast    Bob Michael Jones

User · Answer

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

[mysql] MySQL select with CONCAT condition

Examples related to mysql

Examples related to select

Examples related to conditional-statements

Examples related to where

Examples related to concat