MySQL Concatenation

The MySQL CONCAT() function concatenates one or more arguments together. It returns string concatenating the arguments. If all arguments are nonbinary strings, the result is a nonbinary string and it returns NULL, if any argument is NULL.

Syntax of CONCAT()

CONCAT(str1, str2, str3,..)

where, str1, str2 .. are strings to add together. It requires at least one string parameter otherwise returns an error message. Before concatenating, it converts all parameters to the string type. If any of the parameters contain a NULL value then, it returns NULL value.





MySQL CONCAT_WS() Function

MySQL CONCAL_WS() function is also used for concatenating, but it specially used to add separator between the concatenated strings.

Syntax of CONCAT_WS()

CONCAT_WS(separator,str1, str2, str3,..)

where, separator adds between string parameters.


MySQL GROUP_CONCAT() Function

MySQL GROUP_CONCAT() function is used for concatenating data from multiple rows. This is implemented where we need both GROUP_BY and CONCAT functions. This function returns string value.

Syntax of GROUP_CONCAT()

GROUP_CONCAT(DISTINCT col_name
    ORDER BY col_name
    SEPARATOR sep)




MySQL CONCAT() Exercise

1. Write a mysql statement to find the concatenated first_name, last_name where age of the employee is greater than 30.

Suppose the employee table is -
 
+------------------------+------------------------------+----------+----------+
| first_name             | last_name                    | age      | dept     |
+------------------------+------------------------------+----------+----------+
| Mesa                   | Loop                         |  30      |  Acct    |
| Smith                  | Oak                          |  27      |  Devl    | 
| John                   | Jorz                         |  37      |  QA      | 
| Hary                   | Gaga                         |  32      |  QA      | 
+------------------------+------------------------------+----------+----------+

Solution

The following statement returns the concatenated first_name, last_name of the employees whose age is greater than 30.

mysql> SELECT CONCAT(first_name, ' ', last_name) FROM employee WHERE age > 30;

Output of the above code -

+-----------+
| John Jorz | 
| Hary Gaga |
+-----------+




Related MySQL Exercises

MySQL Get Current Date, User, Version
MySQL Get Maximum Value
MySQL Administrator
MySQL where clause
MySQL order by
MySQL get difference between two dates
MySQL Pattern Matching
MySQL Join
MySQL Regular Expression
MySQL delete duplicate row
MySQL update multiple rows
nth highest salary in MySQL
Display the nth row from MySQL
Commit and rollback in mysql
MySQL SELECT top 5
MySQL display the alternate rows




Read more articles


General Knowledge



Learn Popular Language