
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
EmoticonEmoticon