Tutorial :How do I get first name and last name as whole name in a MYSQL query?



Question:

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 ?


Solution:1

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

SELECT CONCAT_WS(" ", `first_name`, `last_name`) AS `whole_name` FROM `users`  


Solution:2

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.


Solution:3

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;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »