Shulou(Shulou.com)06/01 Report--

1. CONCAT () function

The CONCAT () function is used to concatenate multiple strings into a single string.

Use the data table Info as an example, where the return result of SELECT id,name FROM info LIMIT 1; is

+-+ +

| | id | name |

+-+ +

| | 1 | BioCyc |

+-+ +

1. Grammar and usage characteristics:

CONCAT (str1,str2, …)

Returns a string that results in a connection parameter. If any parameter is NULL, the return value is NULL. There can be one or more parameters.

2. Use examples:

SELECT CONCAT (id,',', name) AScon FROM info LIMIT 1; the returned result is

+-+

| | con |

+-+

| | 1pr BioCyc |

+-+

SELECT CONCAT ('My', NULL,' QL'); the returned result is

+-+

| | CONCAT ('My', NULL,' QL') | |

+-+

| | NULL |

+-+

3. How to specify the separator between parameters

Use the function CONCAT_WS (). The syntax is: CONCAT_WS (separator,str1,str2, …)

CONCAT_WS () stands for CONCAT WithSeparator and is a special form of CONCAT (). The first parameter is the delimiter of the other parameters. The position of the delimiter is placed between the two strings to concatenate. The delimiter can be a string or other parameters. If the delimiter is NULL, the result is NULL. Function ignores the NULL value after any delimiter arguments. But CONCAT_WS () does not ignore any empty strings. (however, all NULL are ignored).

For example, SELECT CONCAT_WS ('_', id,name) AS con_ws FROM info LIMIT 1; the returned result is

+-+

| | con_ws |

+-+

| | 1_BioCyc |

+-+

SELECT CONCAT_WS (',', 'Firstname',NULL,'Last Name'); the returned result is

+-+

| | CONCAT_WS (',', 'First name',NULL,'LastName') | |

+-+

| | First name,Last Name |

+-+

Second, GROUP_CONCAT () function

The GROUP_CONCAT function returns a string result that is composed of value concatenations in the group.

Use the table info as an example, where the return result of the statement SELECT locus,id,journal FROM info WHERE locusIN ('AB086827','AF040764'); is

+-+-+

| | locus | id | journal | |

+-+-+

| | AB086827 | 1 | Unpublished |

| | AB086827 | 2 | Submitted (20-JUN-2002) |

| | AF040764 | 23 | Unpublished |

| | AF040764 | 24 | Submitted (31-DEC-1997) |

+-+-+

1. Usage grammar and characteristics:

GROUP_CONCAT ([DISTINCT] expr [, expr...]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [, col...]]

[SEPARATOR str_val])

In MySQL, you can get the join value of the combination of expressions. Duplicate values can be excluded by using DISTINCT. If you want to sort the values in the results, you can use the ORDER BY clause.

SEPARATOR is a string value that is used to insert into the result value. The default is a comma (","), which can be completely removed by specifying SEPARATOR "".

You can set a maximum length through the variable group_concat_max_len. The syntax executed at run time is as follows: SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer

If the maximum length is set, the resulting value is cut to this maximum length. If the grouped characters are too long, you can set the system parameter: SET @ @ global.group_concat_max_len=40000

2. Use examples:

The return result of the statement SELECT locus,GROUP_CONCAT (id) FROM info WHERE locusIN ('AB086827','AF040764') GROUP BY locus; is

+-+ +

| | locus | GROUP_CONCAT (id) |

+-+ +

| | AB086827 | 1Pol 2 |

| | AF040764 | 23,024 |

+-+ +

The return result of the statement SELECT locus,GROUP_CONCAT (distinct id ORDER BY id DESC SEPARATOR'_') FROM info WHERE locus IN ('AB086827','AF040764') GROUP BY locus; is

+-+

| | locus | GROUP_CONCAT (distinct id ORDER BY id DESC SEPARATOR'_') |

+-+

| | AB086827 | 2: 1 |

| | AF040764 | 24023 |

+-+

Statement SELECT locus,GROUP_CONCAT (concat_ws (',', id,journal) ORDER BY idDESC SEPARATOR'. ') The return result of FROM info WHERE locus IN ('AB086827','AF040764') GROUP BYlocus; is

+-+

| | locus | GROUP_CONCAT (concat_ws (',', id,journal) ORDER BY id DESC SEPARATOR'.') |

+-+

| AB086827 | 2, Submitted (20-JUN-2002). 1. Unmanned |

| | AF040764 | 24, Submitted (31-DEC-1997) .23, Unpublished |

+-+

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.