Application of string functions in MySQL notes _ MySQL

MySQL notes string function application bitsCN.com

String operations are a very important component in programming, while string operations in MySQL databases are quite simple.

Note that all the following functions do not change the original data only when the modified result is returned.

Select a specified number of characters

Mysql> select right ('nihao', 3 );
+ ------------------ +
| RIGHT ('nihao', 3) |
+ ------------------ +
| Hao |
+ ------------------ +
Row in set (0.00 sec)

The RIGHT () function indicates the three characters selected from the string from RIGHT to left.

Similarly, the LEFT () function

SUBSTRING_INDEX: truncates a string.

Mysql> SELECT SUBSTRING_INDEX ('hh, MM, SS', ',', 2 );
+ ----------------------------------- +
| SUBSTRING_INDEX ('hh, MM, SS', ',', 2) |
+ ----------------------------------- +
| HH, MM |
+ ----------------------------------- +
Row in set (0.00 sec)

The first parameter in this function represents the content to be intercepted, and the second parameter represents the truncated content.

The last one is to intercept the nth number, 1 is to intercept the first comma, 2 is to intercept the second

SUBSTRING

Mysql> select substring ('helloworld', 1, 5 );
+ ----------------------------- +
| SUBSTRING ('helloworld', 1, 5) |
+ ----------------------------- +
| Hello |
+ ----------------------------- +
Row in set (0.00 sec)

Intercept 1 ~ 5 Content

UPPER string to uppercase

Mysql> select upper ('Hello ');
+ ---------------- +
| UPPER ('Hello') |
+ ---------------- +
| HELLO |
+ ---------------- +
Row in set (0.00 sec)

LOWER-case LOWER string

Mysql> select lower ('Hello ');
+ ---------------- +
| LOWER ('Hello') |
+ ---------------- +
| Hello |
+ ---------------- +
Row in set (0.00 sec)

REVERSE string

Mysql> select reverse ('Hello ');
+ ------------------ +
| REVERSE ('Hello') |
+ ------------------ +
| Olleh |
+ ------------------ +
Row in set (0.00 sec)

LTRIM clear extra spaces on the left

Mysql> select ltrim ('Hello ');
+ ----------------------- +
| LTRIM ('Hello') |
+ ----------------------- +
| Hello |
+ ----------------------- +
Row in set (0.00 sec)

In addition, RTRIM clears spaces on the right and TRIM clears spaces on both sides.

LENGTH returns the number of characters in the string.

Mysql> select length ('helo ');
+ ---------------- +
| LENGTH ('helo') |
+ ---------------- +
| 4 |
+ ---------------- +
Row in set (0.00 sec)

BitsCN.com