Application of string functions in MySQL notes

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

Copy codeThe Code is as follows: 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.

Copy codeThe Code is as follows: 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

Copy codeThe Code is as follows: 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
Copy codeThe Code is as follows: mysql> select upper ('hello ');
+ ---------------- +
| UPPER ('hello') |
+ ---------------- +
| HELLO |
+ ---------------- +
Row in set (0.00 sec)

LOWER-case LOWER string

Copy codeThe Code is as follows: mysql> select lower ('hello ');
+ ---------------- +
| LOWER ('hello') |
+ ---------------- +
| Hello |
+ ---------------- +
Row in set (0.00 sec)

REVERSE string

Copy codeThe Code is as follows: mysql> select reverse ('hello ');
+ ------------------ +
| REVERSE ('hello') |
+ ------------------ +
| Olleh |
+ ------------------ +
Row in set (0.00 sec)

LTRIM clear extra spaces on the left

Copy codeThe Code is as follows: 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.

Copy codeThe Code is as follows: mysql> select length ('HELO ');
+ ---------------- +
| LENGTH ('HELO') |
+ ---------------- +
| 4 |
+ ---------------- +
Row in set (0.00 sec)