Application of MySQL note string function

String manipulation is a very important part of programming, but the string manipulation in the MySQL database is quite simple

It is important to note that all of the following functions only modify the results when the query returns, without changing the original data

Selects the specified number of characters

mysql> SELECT RIGHT('nihao',3);
+------------------+
| RIGHT('nihao',3) |
+------------------+
| hao              |
+------------------+
 row in set (0.00 sec)

The RIGHT() function here represents the three characters selected from the string from right to left

Similarly, there is the LEFT() function

SUBSTRING_INDEX intercepts strings

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 inside this function represents the content to be intercepted. The second parameter represents what to intercept

The last one is intercepted to which one, the first comma is intercepted to the first comma, and the second comma is intercepted to the second comma

SUBSTRING intercepts strings

mysql> SELECT SUBSTRING('helloworld',1,5);
+-----------------------------+
| SUBSTRING('helloworld',1,5) |
+-----------------------------+
| hello                       |
+-----------------------------+
 row in set (0.00 sec)

The contents of the 1-5 string are intercepted here

Uppercase the UPPER string

mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
 row in set (0.00 sec)

Change the LOWER string to lowercase

mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
 row in set (0.00 sec)

REVERSE reverses strings

mysql> SELECT REVERSE('hello');
+------------------+
| REVERSE('hello') |
+------------------+
| olleh            |
+------------------+
 row in set (0.00 sec)

LTRIM clears excess space on the left

mysql> SELECT LTRIM('  hello     ');
+-----------------------+
| LTRIM('  hello     ') |
+-----------------------+
| hello                 |
+-----------------------+
 row in set (0.00 sec)

In addition, RTRIM clears the space on the right and TRIM clears all Spaces on both sides

LENGTH returns the number of characters in a string

mysql> SELECT LENGTH('helo');
+----------------+
| LENGTH('helo') |
+----------------+
|              4 |
+----------------+
 row in set (0.00 sec)