A brief discussion on the concat function in MySQL. How to add a string before or after a field in MySQL

How to use the concat function in MySQL:

CONCAT(str1,str2,…)

The return value is a string resulting from the concatenation of the parameters. If any parameter is NULL, the return value is NULL.

Notice:

If all arguments are nonbinary strings, the result is a nonbinary string.

If the argument contains any binary string, the result is a binary string.

A numeric argument is converted to its binary string equivalent; to avoid this, use an explicit 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)

When MySQL's concat function concatenates strings, if one of them is NULL, it will return NULL

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

concat_ws function in MySQL

Directions:

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

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first parameter is the separator for the other parameters. The delimiter is placed between the two strings to be concatenated. The separator can be a string or other parameters.

Notice:

If delimiter is NULL, the result is NULL. The function ignores any NULL values ​​following the separator parameter.

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)

Unlike the concat function in MySQL, the concat_ws function will not return NULL due to NULL values ​​when executed.

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

MySQL group_concat function

The complete syntax is as follows:

group_concat([DISTINCT] Fields to be connected [Order BY ASC/DESC sorting fields] [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 and 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 and 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 and print the value of the name field without redundancy in one line.

Comma separated

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 descending 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. In the following example, 'ab' represents the string to be copied, and 2 represents the number of copies.

mysql> select repeat('ab',2);

+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+

1 row in set (0.00 sec)

Another example
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 adds a string before a field in a table

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

This function may be of great help to you! !

The above article briefly talks about the concat function in MySQL and how to add a string before/after a field in MySQL. This is all the content I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.