Mysql_ Connection String Functions

1, Concat

() function

1.1 MySQL's concat function can connect one or more strings, such as

Mysql> Select Concat (' 10 ');

+--------------+

| Concat (' 10 ') |

+--------------+

| 10 |

+--------------+

1 row in Set (0.00 sec)

Mysql> Select concat (' 11 ', ' 22 ', ' 33 ');

+------------------------+

| Concat (' 11 ', ' 22 ', ' 33 ') |

+------------------------+

| 112233 |

+------------------------+

1 row in Set (0.00 sec)

Oracle's Concat function can only connect two strings

Sql> Select Concat (' One ', ' a ') from dual;

1.2 MySQL concat function when the connection string, as long as one of them is null, it will return null

Mysql> Select Concat (' One ', ' n ', null);

+------------------------+

| Concat (' One ', ' + ', null) |

+------------------------+

| NULL |

+------------------------+

1 row in Set (0.00 sec)

When Oracle's Concat function is connected, no null is returned as long as there is a string that is not NULL

Sql> Select Concat (' One ', NULL) from dual;

Concat

--

11

2, Concat_ws () function, representing concat with separator, which is a delimited string connection

Separated by commas, if connected

Mysql> Select Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ');

+-------------------------------+

| Concat_ws (', ', ' 11 ', ' 22 ', ' 33 ') |

+-------------------------------+

| 11,22,33 |

+-------------------------------+

1 row in Set (0.00 sec)

Unlike concat, the CONCAT_WS function does not return NULL because of a null value when it executes

Mysql> Select Concat_ws (', ', ' one ', ' n ', NULL);

+-------------------------------+

| Concat_ws (', ', ' one ', ' + ', NULL) |

+-------------------------------+

| 11,22 |

+-------------------------------+

1 row in Set (0.00 sec)

3, Group_concat () can be used to row to column, Oracle does not have such a function

The complete syntax is as follows

Group_concat ([DISTINCT] field to connect [order by Asc/desc sort field] [Separator ' delimiter '])

The following example

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)

3.1 Group by ID, print the value of the Name field on one line, comma separated (default)

Mysql> Select Id,group_concat (name) from the AA group by ID;

+------+--------------------+

| ID | Group_concat (name) |

+------+--------------------+

| 1 | 10,20,20 |

| 2 | 20 |

| 3 | 200,500 |

+------+--------------------+

3 Rows in Set (0.00 sec)

3.2 In the ID group, the value of the Name field is printed on one line, separated by semicolons

Mysql> Select ID,GROUP_CONCAT (name separator '; ') from the 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)

3.3 In the ID group, the value of the Redundant Name field is printed on one line, separated by commas

Mysql> Select Id,group_concat (distinct name) from the AA group by ID;

+------+-----------------------------+

| ID | Group_concat (distinct name) |

+------+-----------------------------+

| 1 | 10,20 |

| 2 | 20 |

| 3 | 200,500 |

+------+-----------------------------+

3 Rows in Set (0.00 sec)

3.4 Group by ID, print the value of the Name field in one line, comma separated, reverse in name

Mysql> Select Id,group_concat (name order BY name Desc) from the AA group by ID;

+------+---------------------------------------+

| ID | Group_concat (name order BY name Desc) |

+------+---------------------------------------+

| 1 | 20,20,10 |

| 2 | 20 |

| 3 | 500,200 |

+------+---------------------------------------+

3 Rows in Set (0.00 sec)

4, repeat () function, used to copy the string, the following ' AB ' represents the string to be copied, 2 means the number of copies copied

Mysql> Select repeat (' AB ', 2);

+----------------+

| Repeat (' AB ', 2) |

+----------------+

| Abab |

+----------------+

1 row in Set (0.00 sec)

And AS

Mysql> Select repeat (' a ', 2);

+---------------+

| Repeat (' a ', 2) |

+---------------+

| AA |

+---------------+

1 row in Set (0.00 sec)

5, SPACE (N) function. Generates n spaces, such as

Mysql> Select Space (3);

+----------+

| Space (3) |

+----------+

| |

+----------+

1 row in Set (0.00 sec)

6, STRCMP (STR1,STR2) string comparison function, which is related to the character set, the default case-sensitive

If STR1 and STR2 are the same, return 0,

If STR1 is less than STR2, return-1,

Returns 1 if the STR1 is greater than STR2

Mysql> Select strcmp (' abc ', ' abc ');

+---------------------+

| strcmp (' abc ', ' abc ') |

+---------------------+

| 0 |

+---------------------+

1 row in Set (0.00 sec)

Mysql> Select strcmp (' A ', ' ab ');

+------------------+

| strcmp (' A ', ' AB ') |

+------------------+

| -1 |

+------------------+

1 row in Set (0.00 sec)

Mysql> Select strcmp (' abc ', ' AB ');

+--------------------+

| strcmp (' abc ', ' AB ') |

+--------------------+

| 1 |

+--------------------+

1 row in Set (0.00 sec)

Mysql_ Connection String Functions