[sql] How do I get first name and last name as whole name in a MYSQL query?

I want to be able to do something like this

SELECT `first_name` + " " + `last_name` as `whole_name` FROM `users`

So basically I get one column back whole_name which is first_name and last_name concatenated together with a (space).

How do I do that in SQL, or more specifically, MySQL ?

When you have three columns : first_name, last_name, mid_name:

SELECT CASE 
    WHEN mid_name IS NULL OR TRIM(mid_name) ='' THEN
        CONCAT_WS( " ", first_name, last_name ) 
    ELSE 
        CONCAT_WS( " ", first_name, mid_name, last_name ) 
    END 
FROM USER;

You can use a query to get the same:

SELECT CONCAT(FirstName , ' ' , MiddleName , ' ' , Lastname) AS Name FROM TableName;

Note: This query return if all columns have some value if anyone is null or empty then it will return null for all, means Name will return "NULL"

To avoid above we can use the IsNull keyword to get the same.

SELECT Concat(Ifnull(FirstName,' ') ,' ', Ifnull(MiddleName,' '),' ', Ifnull(Lastname,' ')) FROM TableName;

If anyone containing null value the ' ' (space) will add with next value.

rtrim(lastname)+','+rtrim(firstname) as [Person Name]
from Table

the result will show lastname,firstname as one column header !