MySQL SQL character join function
1, Concat () function
1.1 MySQL's Concat function can connect one or more strings, such as
& nbsp; mysql> Select concat (' 10 ');
+--------------+
| Concat (' 10 ') |
+--------------+
| 10 |
+--------------+
1 row in Set (0.00 sec)
Mysql> Select concat (' 11 ', ' 22 ', ' 33 ');
+------------------------+
| Concat (' 11 ', ' 22 ', ' 33 ') |
+------------------------+
| 112233 |
+------------------------+
1 row in Set (0.00 sec)
and Oracle's CONCAT function can only connect two strings
Sql> Select Concat (' One ', ' all ') from dual;
1.2 MySQL's concat function when the string is concatenated, as long as one of them is NULL, NULL is returned
Mysql> Select Concat (' One ', ' all ', null);
+------------------------+
| Concat (' One ', ', ', null) |
+------------------------+
| NULL |
+------------------------+
1 row in Set (0.00 sec)
When Oracle's Concat function is connected, as long as there is a string that is not NULL, it does not return
Null
Sql> Select Concat (' One ', NULL) from dual;
Concat
--
11
2, Concat_ws () function, representing concat with separator, that is, a delimited string connection
Separated by commas after connection
Mysql> Select Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ');
+-------------------------------+
| Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in Set (0.00 sec)
Unlike concat, the CONCAT_WS function does not return NULL because of a null value at the time it is executed.
Mysql> Select Concat_ws (', ', ', ', ', ');
+-------------------------------+
| Concat_ws (', ', ' One ', ' ', ', NULL ') |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in Set (0.00 sec)
3, Group_concat () can be used for row-column, Oracle does not have such a function
The complete syntax is as follows
Group_concat ([DISTINCT] The field to connect to [order by asc/desc sort field]
[Separator ' delimiter '])
The following example
Mysql> select * from AA;
+------+------+
| ID | name |
+------+------+
| 1 | 10 |
| 1 | 20 |
| 1 | 20 |
| 2 | 20 |
| 3 | 200 |
| 3 | 500 |
+------+------+
6 rows in Set (0.00 sec)
3.1 Group by ID, print the value of the Name field on one line, comma separated (default)
Mysql> Select Id,group_concat (name) from AA Group by ID;
+------+--------------------+
| ID | Group_concat (name) |
+------+--------------------+
| 1 | 10,20,20 |
| 2 | 20 |
| 3 | 200,500 |
+------+--------------------+
3 Rows in Set (0.00 sec)
3.2 Grouped by ID, print the value of the Name field on one line, separated by semicolons
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.00 sec)
3.3 Grouped by ID, the value of the Name field to be redundant is printed on one line, separated by commas
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.00 sec)
3.4 Group by ID, print the value of the Name field on one line, comma delimited, and order by name
Mysql> Select Id,group_concat (name order BY name Desc) from 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)
4, repeat () function, to copy the string, the following ' AB ' represents the string to be copied, 2 indicates the number of copies copied
Mysql> Select repeat (' AB ', 2);
+----------------+
| Repeat (' AB ', 2) |
+----------------+
| Abab |
+----------------+
1 row in Set (0.00 sec)
And AS
Mysql> Select repeat (' a ', 2);
+---------------+
| Repeat (' a ', 2) |
+---------------+
| AA |
+---------------+
1 row in Set (0.00 sec)