one, CONCAT () functionThe CONCAT () function is used to concatenate multiple strings into a single string. Using data table info as an example, where select Id,name from Info LIMIT 1, the return result is +----+--------+| ID | Name |+----+--------+| 1 | BIOCYC |+----+--------+ 1, Syntax and usage features: CONCAT (str1,str2,...) Returns the string that results from the connection parameter,if any one of the arguments is NULL, the return value is NULL,can have one or more parameters. 2, use example: SELECT CONCAT (ID, ', ', name) as con from Info LIMIT 1; return result is +----------+| Con |+----------+| 1,BIOCYC |+----------+ SELECT CONCAT (' My ', NULL, ' QL '); return result is +--------------------------+| CONCAT (' My ', NULL, ' QL ') |+--------------------------+| NULL |+--------------------------+ 3, how to specify the delimiter between parameters using the function Concat_ws (). Use syntax for: CONCAT_WS (separator,str1,str2,...) Concat_ws () represents CONCAT with Separator, which is a special form of the CONCAT (). The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be another parameter.if the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters.however, CONCAT_WS () does not ignore any empty strings. (All NULL is ignored, however)。 such as Select Concat_ws (' _ ', Id,name) as Con_ws from info LIMIT 1; return result is +----------+| Con_ws |+----------+| 1_BIOCYC |+----------+ SELECT concat_ws (', ', ' First name ', NULL, ' last Name '); return result is +---------------------------------- ------------+| Concat_ws (', ', ' First name ', NULL, ' last name ') |+----------------------------------------------+| First Name,last name |+----------------------------------------------+
Second, Group_concat () functionThe group_concat function returns a string result that is combined by a value in the groupingUsing table info as an example, where the statement select Locus,id,journal from info where locus in (' AB086827 ', ' AF040764 '), the return result is +----------+----+----- ---------------------+| locus | ID | journal |+----------+----+--------------------------+| AB086827 | 1 | unpublished | | AB086827 | 2 | Submitted (20-jun-2002) | | AF040764 | 23 | unpublished | | AF040764 | 24 | Submitted (31-dec-1997) |+----------+----+--------------------------+ 1, usage syntax and features: Group_concat ([ DISTINCT] expr [, expr ...] [ORDER by {Unsigned_integer | col_name | formula} [ASC | DESC] [col ...] [SEPARATOR Str_val]) In MySQL, you can get the link value of the expression binding. You can exclude duplicate values 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 (",") , you can completely remove this delimiter by specifying SEPARATOR "". You can set a maximum length by using the variable Group_concat_max_len. The syntax to execute 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 clipped to this maximum length. If the characters in the grouping are too long, you can set the system parameters: set @ @global. group_concat_max_len=40000; 2, using example: statement SELECT Locus,group_concat (ID) from Info WHERE locus in (' AB086827 ', ' AF040764 ') GROUP by locus; The return result is +----------+------------------+| locus | Group_concat (ID) |+----------+------------------+| AB086827 | 1,2 | | AF040764 | 23,24 |+----------+------------------+ Statement SELECT LOCUS,GROUP_CONCAT (DISTINCT ID ORDER by Id desc SEPARATOR ' _ ') from Info WHERE locus in (' AB086827 ', ' A F040764 ') GROUP by locus; return result is +----------+----------------------------------------------------------+| locus | GROUP_CONCAT (DISTINCT ID ORDER by ID DESC separator ' _') |+----------+----------------------------------------------------------+| AB086827 | 2_1 | | AF040764 | 24_23 |+------ ----+----------------------------------------------------------+ statement Select Locus,group_concat (Concat_ws (', ') , id,journal) ORDER by id DESC SEPARATOR '. ') from Info WHERE locus in (' AB086827 ', ' AF040764 ') GROUP by locus; The return result is +----------+--------------------------------------------------------------------------+| locus | Group_concat (Concat_ws (', ', id,journal) ORDER by id DESC SEPARATOR '. ') |+----------+--------------------------------------------------------------------------+| AB086827 | 2, submitted (20-jun-2002). 1, unpublished | | AF040764 | Submitted (31-dec-1997). unpublished , |+----------+------------------------- -------------------------------------------------+ reproduced in MySQL function CONCAT and group_concat
[]mysql functions CONCAT and Group_concat
