Detailed explanation of the use of MySQL concatenation function CONCAT

Detailed explanation of the use of MySQL concatenation function CONCAT

The previous articles introduced the replacement function (Replace) and the segmentation function (SubString) in MySQL. Today, let’s take a look at the MySQL professional “string concatenation” function: concat. As usual, if you have any good suggestions and ideas, please remember to write them in the comments. When I am slacking off at work, I will share the experience with you all~

1. Several usages of concat function

1-1. Function: concat(str1,str2,…)

The concat function is generally used in the SELECT query syntax to modify the returned field content. For example, there is a LOL hero information table as follows

mysql> select * from `LOL`;
+----+---------------+--------------+-------+
| id | hero_title | hero_name | price |
+----+---------------+--------------+-------+
| 1 | DBlade Shadow | Talon | 6300 |
| 2 | X-Swift Scout | Teemo | 6300 |
| 3 | G Radiant Lady | Lux | 1350 |
| 4 | F Clockwork Spirit | Orianna | 6300 |
| 5 | Z Supreme Fist | Lee Sin | 6300 |
| 6 | W: Blademaster | Easy | 450 |
| 7 | J Swift Blade Master | Yasuo | 450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

I need to return a column: hero title - hero name data, this is where the concat function is used, as follows:

SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`;
mysql> SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`;
+------------------------------+-------+
| full_name | price |
+------------------------------+-------+
| DBlade Shadow - Talon | 6300 |
| X-Swift Scout - Teemo | 6300 |
| G Radiant Lady - Lux | 1350 |
| F Clockwork Spirit - Orianna | 6300 |
| Z Supreme Fist - Lee Sin | 6300 |
| W Wujuejiansheng - Easy | 450 |
| J Swift Swordsman - Yasuo | 450 |
+------------------------------+-------+
7 rows in set (0.00 sec)

If there is NULL in the spliced ​​parameters, NULL is returned; as follows:

SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`;
mysql> SELECT CONCAT(hero_title,'NULL',hero_name) as full_name, price from `LOL`;
+-------------------------------+-------+
| full_name | price |
+-------------------------------+-------+
| DBlade Shadow NULLTalon | 6300 |
| X-Swift Scout NULL Teemo | 6300 |
| G Radiant Lady NULL Lux | 1350 |
| F Clockwork Spirit NULL Orianna | 6300 |
| Z Supreme Fist NULL Lee Sin | 6300 |
| W NULL Yi | 450 |
| J Swift Sword Master NULL Yasuo | 450 |
+-------------------------------+-------+
7 rows in set (0.00 sec)

insert image description here

Sorry, the above is written by my brother, the correct one is as follows:

mysql> SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`;
+-----------+------+
| full_name | price |
+-----------+------+
| NULL | 6300 |
| NULL | 6300 |
| NULL | 1350 |
| NULL | 6300 |
| NULL | 6300 |
| NULL | 450 |
| NULL | 450 |
+-----------+------+
7 rows in set (0.00 sec)

1-2. Function: concat_ws(separator,str1,str2,…)

CONCAT_WS() function full name: CONCAT With Separator, which is a special form of CONCAT(). The first parameter (separator) is the separator for the other parameters. The position of the delimiter is between the two strings to be concatenated.分隔符可以是一個字符串，也可以是其它字段參數。

It should be noted that:

如果分隔符為NULL，則結果為NULL；但如果分隔符后面的參數為NULL，只會被直接忽略掉，而不會導致結果為NULL。

OK, let's still use the LOL table above, concatenate the fields, and separate them with commas:

select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`;
mysql> select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`;
+---------------------------------+-------+
| full_name | price |
+---------------------------------+-------+
| DBlade Shadow,Talon,6300 | 6300 |
| X-Swift Scout,Teemo,6300 | 6300 |
| G Radiant Lady, Lux, 1350 | 1350 |
| F Clockwork Spirit, Orianna, 6300 | 6300 |
| Z Supreme Fist, Lee Sin, 6300 | 6300 |
| W Wuju Swordmaster, Easy, 450 | 450 |
| J Swift Swordsman, Yasuo, 450 | 450 |
+---------------------------------+-------+
7 rows in set (0.00 sec)

When the concatenation parameter after the delimiter is NULL, it is directly ignored and will not affect the overall result, as follows:

select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`;
mysql> select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`;
+----------------------------+-------+
| full_name | price |
+----------------------------+-------+
| DBlade Shadow, Talon | 6300 |
| X-Swift Scout, Teemo | 6300 |
| G Radiant Lady, Lux | 1350 |
| F Clockwork Genie, Orianna | 6300 |
| Z Supreme Fist, Lee Sin | 6300 |
| W Wuju Swordmaster, Easy | 450 |
| J Swift Swordsman, Yasuo | 450 |
+----------------------------+-------+
7 rows in set (0.00 sec)

When the delimiter is NULL, the result returns NULL, as follows:

select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`;
mysql> select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`;
+-----------+------+
| full_name | price |
+-----------+------+
| NULL | 6300 |
| NULL | 6300 |
| NULL | 1350 |
| NULL | 6300 |
| NULL | 6300 |
| NULL | 450 |
| NULL | 450 |
+-----------+------+
7 rows in set (0.00 sec)

1-3. Function: group_concat(expr)

group_concat ( [DISTINCT] field name [order by sort field ASC/DESC] [Separator 'separator'] )

The group_concat function is usually used in query statements with group by. group_concat is generally included in the query return result field.

Does the formula of the group_concat function look complicated? Let's take a look at the above formula. The brackets [] are optional, indicating that they can be used or not.

  • 1. [DISTINCT] : Supports deduplication of splicing parameters;
  • 2. [Order by] : The concatenated parameters support sorting function;
  • 3. [Separator] : You are very familiar with this. It supports customizing the "separator". If you do not set it, the default is no separator;

Okay, let’s get into the fun testing phase. Let’s use this LOL table again, don’t ask why. Maybe this is youth! I still remember that year when we played five-man team all night, the internet cafe lost power during the big dragon fight...

insert image description here

Off topic again. . Excuse me.

mysql> select * from `LOL`;
+----+---------------+--------------+-------+
| id | hero_title | hero_name | price |
+----+---------------+--------------+-------+
| 1 | DBlade Shadow | Talon | 6300 |
| 2 | X-Swift Scout | Teemo | 6300 |
| 3 | G Radiant Lady | Lux | 1350 |
| 4 | F Clockwork Spirit | Orianna | 6300 |
| 5 | Z Supreme Fist | Lee Sin | 6300 |
| 6 | W: Blademaster | Easy | 450 |
| 7 | J Swift Blade Master | Yasuo | 450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

For example, we need to distinguish heroes in different price ranges. If we don’t use group_concat, we may have to use order by to distinguish them.

select * from `LOL` order by price desc;
mysql> select * from `LOL` order by price desc;
+----+---------------+--------------+-------+
| id | hero_title | hero_name | price |
+----+---------------+--------------+-------+
| 1 | DBlade Shadow | Talon | 6300 |
| 2 | X-Swift Scout | Teemo | 6300 |
| 4 | F Clockwork Spirit | Orianna | 6300 |
| 5 | Z Supreme Fist | Lee Sin | 6300 |
| 3 | G Radiant Lady | Lux | 1350 |
| 6 | W: Blademaster | Easy | 450 |
| 7 | J Swift Blade Master | Yasuo | 450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

But this is not very intuitive. I want to see every line. What should I do?

SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price 
 from `LOL` GROUP BY price ORDER BY price desc;

At this time, the GROUP_CONCAT function can easily help you solve this problem. Look, isn't it comfortable?

Note : Here I concatenate the three parameters (hero_title,' - ', hero_name), set the separator to: ',', group by price, and sort by price. The effect is as follows

mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price desc;
+------------------------------------------------------------------------+-------+
| full_name | price |
+------------------------------------------------------------------------+-------+
| D Blade's Shadow - Talon, X Swift Scout - Teemo, F Clockwork - Orianna, Z High Fist - Lee Sin | 6300 |
| G Radiant Lady - Lux | 1350 |
| W Wujuejiansheng - Yi, J Swift Swordsman - Yasuo | 450 |
+------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

If you want to sort by price from small to large, you only need to control the outer ORDER BY, as follows:

SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price 
  from `LOL` GROUP BY price ORDER BY price asc;
mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
+-------------------------------------------------------------------------+-------+
| full_name | price |
+-------------------------------------------------------------------------+-------+
| W Wujuejiansheng - Yi, J Swift Swordsman - Yasuo | 450 |
| G Radiant Lady - Lux | 1350 |
| D Blade's Shadow - Talon, X Swift Scout - Teemo, F Clockwork - Orianna, Z High Fist - Lee Sin | 6300 |
+-------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

So how do you use the order by sort in the GROUP_CONCAT function? It is used to sort the spliced ​​fields, such as sorting and splicing according to hero_title, as follows:

SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
+-------------------------------------------------------------------------+-------+
| full_name | price |
+-------------------------------------------------------------------------+-------+
| J Swift Blade Master - Yasuo, W Infinity Blade Master - Yi | 450 |
| G Radiant Lady - Lux | 1350 |
| D Blade's Shadow - Talon, F Clockwork Spirit - Orianna, X Swift Scout - Teemo, Z Supreme Fist - Lee Sin | 6300 |
+-------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

Well, it can be seen that the concat function in MySQL is still very useful. I hope you will think of it when you have similar needs or confusions! Or you may find this blog and it will help you!

This is the end of this article about the detailed explanation of the use of the MySQL concatenation function CONCAT. For more related MySQL concatenation function CONCAT content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Detailed explanation of the efficiency comparison of two methods of batch updating data in mybatis
  • Detailed explanation of the use of @TableField annotation in MybatisPlus
  • Instructions for using bind tags and concat in mybatis

<<:  The contents of the table in HTML are displayed horizontally and vertically in the center

>>:  How to solve the problem of insufficient permissions when switching users in docker container

Recommend

VMware configuration hadoop to achieve pseudo-distributed graphic tutorial

1. Experimental Environment serial number project...

How to install vim editor in Linux (Ubuntu 18.04)

You can go to the Ubuntu official website to down...

Img semi-transparent processing Img plus semi-transparent background implementation ideas and code

Copy code The code is as follows: <!DOCTYPE ht...

Why developers must understand database locks in detail

1.Lock? 1.1 What is a lock? The real meaning of a...

JS, CSS and HTML to implement the registration page

A registration page template implemented with HTM...

Start nginxssl configuration based on docker

Prerequisites A cloud server (centOS of Alibaba C...

Measured image HTTP request

Please open the test page in a mainstream browser...

Detailed explanation of custom configuration of docker official mysql image

In order to save installation time, I used the of...

Detailed explanation of how to prevent content from being selected, copied, or right-clicked in HTML pages

Sometimes, we don't want the content presente...

Several ways to implement inheritance in JavaScript

Table of contents Structural inheritance (impleme...

Implement MaterialUI button click animation based on CSS and encapsulate it into a React component

Preface As a heavy user of front-end frameworks, ...

Detailed tutorial on deploying SpringBoot + Vue project to Linux server

Preface Let me share with you how I deployed a Sp...

CSS Tutorial: CSS Attribute Media Type

One of the most important features of a style she...

How a select statement is executed in MySQL

Table of contents 1. Analyzing MySQL from a macro...

Analysis of the process of building a LAN server based on http.server

I don’t know if you have ever encountered such a ...