In the development process, it is sometimes encountered that only a part of a certain field of the database is required. Sometimes, this scenario is more convenient and faster to operate directly through the database than through code. MySQL has many string functions that can be used to deal with these requirements, such as left, right, substring and substring_index
1. Intercept the string left(str, length) from the left
str: intercepted string
Length: intercept length. If it is negative, nothing will be returned
(1) length is a positive number
mysql> SELECT LEFT("www.baidu.com", 3);
+--------------------------+
| LEFT("www.baidu.com", 3) |
+--------------------------+
| www |
+--------------------------+
1 row in set (0.00 sec)
(2) length is a negative number
mysql> SELECT LEFT("www.baidu.com", -3);
+---------------------------+
| LEFT("www.baidu.com", -3) |
+---------------------------+
| |
+---------------------------+
1 row in set (0.00 sec)
2. Intercept the string right(str, length) from the right
str: intercepted string
Length: intercept length. If it is negative, nothing will be returned
(1) length is a positive number
mysql> SELECT RIGHT("www.baidu.com", 3);
+---------------------------+
| RIGHT("www.baidu.com", 3) |
+---------------------------+
| com |
+---------------------------+
1 row in set (0.00 sec)
(2) length is a negative number
mysql> SELECT RIGHT("www.baidu.com", -3);
+----------------------------+
| RIGHT("www.baidu.com", -3) |
+----------------------------+
| |
+----------------------------+
1 row in set (0.00 sec)
3. Intercept a string of specific length substring(str, pos, length)
str: intercepted string
pos: intercept from the last few digits. If it is negative, intercept from left to right from the last few characters
Length: intercept length. If it is negative, nothing will be returned
(1) Intercept from the fifth character to the right until the end
mysql> SELECT SUBSTRING("www.baidu.com", 5);
+-------------------------------+
| SUBSTRING("www.baidu.com", 5) |
+-------------------------------+
| baidu.com |
+-------------------------------+
1 row in set (0.00 sec)
(2) Intercept from the penultimate character to the right until the end
mysql> SELECT SUBSTRING("www.baidu.com", -5);
+--------------------------------+
| SUBSTRING("www.baidu.com", -5) |
+--------------------------------+
| u.com |
+--------------------------------+
1 row in set (0.00 sec)
(3) Intercept 5 characters from the 5th character to the right
mysql> SELECT SUBSTRING("www.baidu.com", 5, 5);
+----------------------------------+
| SUBSTRING("www.baidu.com", 5, 5) |
+----------------------------------+
| baidu |
+----------------------------------+
1 row in set (0.00 sec)
(4) Intercept 5 characters from the penultimate character to the right
mysql> SELECT SUBSTRING("www.baidu.com", -5, 5);
+-----------------------------------+
| SUBSTRING("www.baidu.com", -5, 5) |
+-----------------------------------+
| u.com |
+-----------------------------------+
1 row in set (0.00 sec)
(5) length is a negative number
mysql> SELECT SUBSTRING("www.baidu.com", 5, -5);
+-----------------------------------+
| SUBSTRING("www.baidu.com", 5, -5) |
+-----------------------------------+
| |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING("www.baidu.com", -5, -5);
+------------------------------------+
| SUBSTRING("www.baidu.com", -5, -5) |
+------------------------------------+
| |
+------------------------------------+
1 row in set (0.00 sec)
4. Intercept string substring by keyword_ index(str, delim, count)
str: intercepted string
Delim: keyword that splits str from the count delim
Count: the number of times the keyword appears. If it is a positive number, the count delim from left to right will split str and return the left content; if it is a negative number, the count delim from right to left will split str and return the right content
(1) Intercept the first "." All previous characters
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', 1) |
+------------------------------------------+
| www |
+------------------------------------------+
1 row in set (0.00 sec)
(2) Intercept the second "." All previous characters
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', 2) |
+------------------------------------------+
| www.baidu |
+------------------------------------------+
1 row in set (0.00 sec)
(3) Intercept the penultimate "." All characters after
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', -1);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', -1) |
+-------------------------------------------+
| com |
+-------------------------------------------+
1 row in set (0.00 sec)
(4) Intercept the penultimate "." All characters after
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '.', -2) |
+-------------------------------------------+
| baidu.com |
+-------------------------------------------+
1 row in set (0.00 sec)
(5) If the keyword does not exist, the entire string is returned
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '*', 1);
+------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '*', 1) |
+------------------------------------------+
| www.baidu.com |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '*', -1);
+-------------------------------------------+
| SUBSTRING_INDEX('www.baidu.com', '*', -1) |
+-------------------------------------------+
| www.baidu.com |
+-------------------------------------------+
1 row in set (0.00 sec)
(6) Intercept "baidu"
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com', '.', -2), '.', 1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com', '.', -2), '.', 1) |
+--------------------------------------------------------------------+
| baidu |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)