Concat () related functions in MySQL

Basic application of concat function one:

The SQL concat function is used to concatenate two strings together to form a single string. Try the following example: SQL> SELECT CONCAT (' first ', ' SECOND ');+----------------------------+| CONCAT (' first ', ' SECOND ') |+----------------------------+| First SECOND |+----------------------------+1 row in Set (0.00sec) To learn more about the concat function, consider EMPLOYEE_TBL tables with the following records: SQL> SELECT *From Employee_tbl;+------+------+------------+--------------------+| ID | name | Work_date |    Daily_typing_pages |+------+------+------------+--------------------+| 1 | John |                2007-01-24 |    250 | | 2 | Ram |                2007-05-27 |    220 | | 3 | Jack |                2007-05-06 |    170 | | 3 | Jack |                2007-04-06 |    100 | | 4 | Jill |                2007-04-06 |    220 | | 5 | Zara |                2007-06-06 |    300 | | 5 | Zara |                2007-02-06 | |+------+------+------------+--------------------+7 rows in Set (0.00sec) Now, assuming that you want to connect the employee ID and work_date based on the table above, you can use the following command: SQL>SELECT CONCAT (ID, name, work_date)-From Employee_tbl;+-----------------------------+| CONCAT (ID, name, work_date) |+-----------------------------+| 1john2007-01-24 | | 2ram2007-05-27 | | 3jack2007-05-06 | | 3jack2007-04-06 | | 4jill2007-04-06 | | 5zara2007-06-06 | | 5zara2007-02-06 |+-----------------------------+7 rows in Set (0.00 sec)

Basic usage of CONCAT_WS

How to use the Concat_ws function in MySQL: Concat_ws (separator,str1,str2,...) Concat_ws () represents CONCAT with Separator, which is a special form of the CONCAT (). The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be another parameter. Note: If the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters. If the connection is comma-separated MySQL> select Concat_ws (', ', ' One ', ' ', ', ') '; +-------------------------------+| Concat_ws (', ', ' one ', ' a ', ' |+ ')-------------------------------+| 11,22,33 |+-------------------------------+1 row in Set (0.00concat_ws function does not return NULL for null values when it is executed. MySQL > select Concat_ws (', ', ' one ', ' + ',NULL); +-------------------------------+| Concat_ws (', ', ' one ', ' |+ ', NULL) the-------------------------------+| 11,22 |+-------------------------------+1 row in Set (0.00 sec)

Basic application of Group_concat () function

The complete syntax for the GROUP_CONCAT function in MySQL is as follows: Group_concat ([DISTINCT] field to connect [order by ASC/desc sort field] [Separator ' delimiter ']) basic query MySQL> select *From AA;+------+------+| id| Name |+------+------+|1 | 10| | 1 | 20| | 1 | 20| | 2 | 20| | 3 | 200 | | 3 | |+------+------+6 rows in Set (0.00sec) Group by ID, print the value of the Name field on one line, comma separated (default) MySQL>Select Id,group_concat (name) from the AA group by ID;+------+--------------------+| id| Group_concat (name) |+------+--------------------+|1 | 10,20,20| | 2 | 20 | | 3 | 200,500|+------+--------------------+3 rows in Set (0.00sec) Group by ID, print the value of the Name field on one line, semicolon separate MySQL> select ID,GROUP_CONCAT (Name separator '; ')) from the AA group by ID;+------+----------------------------------+| id| Group_concat (name separator '; ') |+------+----------------------------------+|1 | 10;20;20 | | 2 | 20| | 3 | 200;500 |+------+----------------------------------+3 rows in Set (0.00sec) in the ID group, print the value of the redundant Name field to a single line, comma-separated MySQL>Select Id,group_concat (distinct name) from the AA group by ID;+------+-----------------------------+| id| Group_concat (distinct name) |+------+-----------------------------+|1 | 10,20| | 2 | 20 | | 3 | 200,500 |+------+-----------------------------+3 rows in Set (0.00sec) Group by ID, print the value of the Name field on one line, comma separated, and reverse the MySQL in name>Select Id,group_concat (name order BY name Desc) from the AA group by ID;+------+---------------------------------------+| id| Group_concat (name order BY name Desc) |+------+---------------------------------------+|1 | 20,20,10 | | 2 | 20| | 3 | 500,200|+------+---------------------------------------+3 rows in Set (0.00 sec)

Concat () related functions in MySQL