A brief introduction to the concat function in mysql and the sample code for adding strings before/after mysql fields

The following editor will bring you a brief discussion on mysqlIn the concat function, mysql adds string before/after the field. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor and take a look.

How to use the concat function in MySQL:

##CONCAT(str1,str2,…)

The return result is the string generated by the connection parameters. If any parameter is

NULL, the return value is NULL.

Note:

If all arguments are non-binary strings, the result is a non-binary string.

If the self

variable contains any binary string, the result is a binary string.

A numeric parameter is converted to its equivalent binary string format; to avoid this, use an explicit type cast, for example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col)

MySQL’s concat function can connect one or more strings, such as

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)

MySQL’s concat function can connect string, as long as one of them is NULL, then NULL will be returned

mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

concat_ws function in MySQL

Usage:

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

CONCAT_WS() represents CONCAT With Separator, which is a special form of CONCAT(). The first parameter is

the delimiter for other parameters. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string or other parameters.

Note:

If the delimiter is NULL, the result is NULL. The function ignores NULL values ​​after any delimiter argument.

If the connection is separated by commas

mysql> select concat_ws(',','11','22','33');
+-------------------------------+
| concat_ws(',','11','22','33') |
+-------------------------------+
| 11,22,33 |
+-------------------------------+
1 row in set (0.00 sec)

Different from the concat function in MySQL, when the concat_ws function is executed, it will not return NULL due to a NULL value

mysql> select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)

group_concat function in MySQL

The complete syntax is as follows:

group_concat([DISTINCT] Fields to be connected [Order BY ASC/DESC sorting field] [Separator 'separator'])

Basic query

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)

Group by id, Print the value of the name field in one line, separated by commas (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)

Group by id, print the value of the name field in one line, separated by semicolons

mysql> select id,group_concat(name separator ';') from 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)

Group by id, print the value of the redundant name field in one line,

separated by commas

mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

Group by id, print the value of the name field in one line, separated by commas, in reverse 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)

repeat() function

Used to copy a string, the following 'ab' represents the string to be copied, 2 represents the number of copies

mysql> select repeat('ab',2);
+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+
1 row in set (0.00 sec)
又如mysql> select repeat('a',2);
+---------------+
| repeat('a',2) |
+---------------+
| aa |
+---------------+
1 row in set (0.00 sec)

mysql appends a string to a field in the table:

update table_name set field=CONCAT(field,'',str)

mysql Add a string before a field in the table

update table_name set field=CONCAT('str',field)

This function may be of great help to you! !

The above is the detailed content of A brief introduction to the concat function in mysql and the sample code for adding strings before/after mysql fields. For more information, please follow other related articles on the PHP Chinese website!