Shulou(Shulou.com)05/31 Report--

Mysql in how to concatenate functions, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1.repeat () function

How to use it:

REPEAT (str,count) str: string or corresponding value; count: several numeric values, which is the number of previous strings.

Example 1: mysql > select repeat ('ab',3)

+-+

| | repeat ('ab',3) | |

+-+

| | ababab |

+-+

1 row in set (0.00 sec)

Example: mysql > select repeat (name,2) from table_name

+-+

| | repeat (name,2) |

+-+

| | aaaa |

| | bbbb |

| | cccc |

+-+

3 row in set (0.00 sec)

Parsing: used to copy the string, the following 'ab'' indicates the string to be copied, and the following number, such as 3, indicates the number of copies.

2. Group_concat function

The complete syntax is as follows:

Group_concat ([DISTINCT] the field to be connected [Order BY ASC/DESC sort field] [Separator 'delimiter'])

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, print the values of the name field on a line, separated by commas (default)

Mysql > select id,group_concat (name) from aa group by id

+-+ +

| | id | group_concat (name) |

+-+ +

| | 1 | 10, 20, 20, 20 |

| | 2 | 20 |

| | 3 | 200500 | |

+-+ +

3 rows in set (0.00 sec)

Group the values in id and print the values of the name field on a 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 values of the de-redundant name fields on one line

Comma separation

Mysql > select id,group_concat (distinct name) from aa group by id

+-- +

| | id | group_concat (distinct name) |

+-- +

| | 1 | 100.20 |

| | 2 | 20 |

| | 3 | 200500 | |

+-- +

3 rows in set (0.00 sec)

Group the values in id, print the values of the name field on one line, separated by commas, and sort them in reverse order in 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 | 500200 | |

+-- +

3 rows in set (0.00 sec)

3.RTrim () function

Select concat (RTrim (name),'(', RTrim (country),') from table_name order by name

Example 1:

Select concat (LTRIM ('werrrt'),'(', RTRIM (2343),'))

+-+

| | id | concat (LTRIM ('werrrt'),'(', TRIM (2343),')') |

+-+

| | 1 | werrrt (2343) |

+-+

1 rows in set (0.00 sec)

Parsing: LTrim () removes the space on the left side of the string.

Example 2:

Select concat (RTRIM ('werrrt'),'(', RTRIM (2343),'))

+-+

| | id | concat (LTRIM ('werrrt'),'(', TRIM (2343),')') |

+-+

| | 1 | werrrt (2343) |

+-+

1 rows in set (0.00 sec)

Parsing: RTrim () removes the space to the right of the string. Trim () removes the spaces on the left and right sides of the string.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.