Concatenate multiple mysql Fields
The following two functions can be used to concatenate the query result line Fields of Mysql:
1. concat Function
mysql> select concat('1','2','3') from test ;+---------------------+| concat('1','2','3') |+---------------------+| 123 |+---------------------+
If NULL exists in the connection string, the returned result is NULL:
mysql> select concat('1','2',NULL,'3') from test ;+--------------------------+| concat('1','2',NULL,'3') |+--------------------------+| NULL |+--------------------------+
2. concat_ws Function
Concat (separator, str1, str2,...) represents concat with separator, which is a special form of concat. The first parameter is the delimiter of other parameters. The separator is placed between the two strings to be connected. The delimiter can be a string or another parameter.
mysql> select concat_ws(':','1','2','3') from test ;+----------------------------+| concat_ws(':','1','2','3') |+----------------------------+| 1:2:3 |+----------------------------+
If the Delimiter is NULL, the returned result is NULL:
mysql> select concat_ws(NULL,'1','2','3') from test; +-----------------------------+| concat_ws(NULL,'1','2','3') |+-----------------------------+| NULL | +-----------------------------+
If NULL exists in the parameter, it will be ignored:
mysql> select concat_ws(':','1','2',NULL,NULL,NULL,'3') from test ;+-------------------------------------------+| concat_ws(':','1','2',NULL,NULL,NULL,'3') |+-------------------------------------------+| 1:2:3 |+-------------------------------------------+
NULL can be judged and replaced with other values.
mysql> select concat_ws(':','1','2',ifNULL(NULL,'0'),'3') from bank limit 1; +---------------------------------------------+| concat_ws(':','1','2',ifNULL(NULL,'0'),'3') |+---------------------------------------------+| 1:2:0:3 | +---------------------------------------------+