Directory
1. LEFT() function
Two, RIGHT () function
Three, SUBSTRING () function
Four, SUBSTRING_INDEX () function
5. Practical operation
MySQL string interception functions mainly include: left(), right(), substring(), substring_index(). Each has its usage scenarios. Today, let me take you a few minutes to familiarize yourself with them, Mark!
One, LEFT() function
LEFT(string,length) , starting from the first digit on the left of the string string, the length of the interception is length characters. length should be greater than 0, if <=0, return an empty string. Examples are as follows:
mysql> SELECT LEFT('www.csdn.net',5) from web_info w;
+ ------------------+
| LEFT('www.csdn.net',5) |
+ ------------------+
| www.c |
+ ------------------+
1 row in set (0.00 sec)
Two, RIGHT() function
LEFT(string,length) , starting from the first character on the right of the string string, the length of the interception is length characters, and the interception is still in the forward order. length should be greater than 0, if <=0, return an empty string.
mysql> SELECT RIGHT('www.csdn.net',5) from web_info w;
+ -------------------------+
| RIGHT('www.csdn.net',5) |
+ -------------------------+
| n.net |
+ -------------------------+
1 row in set (0.00 sec)
3. SUBSTRING() function
substring() is a function specially used to split strings. There are two main forms:
- SUBSTRING(string, position);
- SUBSTRING(string,position,length);
The following is the SQL standard version of the above two types of statements, the meaning is the same, it is longer, but more expressive.
SUBSTRING(string FROM position); SUBSTRING(string FROM position FOR length);
Let’s take a look at the two usage examples of SUBSTRING();
1. SUBSTRING(string,position)
- string: The parameter is the string to operate on.
- position: The parameter is an
"integer", which is used to specify the starting character of the substring, and position can be a positive or negative integer.If the position is greater than the length of the operation string, an empty string will be returned.
For example, to obtain a substring from the string “www.csdn.net”: “csdn.net”, the position of the substring must start from 5, as in the following SELECT statement:
mysql> SELECT substring('www.csdn.net',5) from web_info w;
+ -----------------------------+
| substring('www.csdn.net',5) |
+ -----------------------------+
| csdn.net |
+ -----------------------------+
1 row in set (0.00 sec)
2. SUBSTRING(string,position,length)
In addition to the string and position parameters, the SUBSTRING function also has a length parameter. length is a positive integer that specifies the number of characters in the substring. If length<=0, an empty string will be returned.
For example, to obtain csdn in www.csdn.net, the SQL is as follows:
mysql> SELECT substring('www.csdn.net',5,4) from web_info w;
+ -------------------------------+
| substring('www.csdn.net',5,4) |
+ -------------------------------+
| csdn |
+ -------------------------------+
1 row in set (0.00 sec)
Or by configuring the position, count from the back to the front; the SQL is as follows:
mysql> SELECT substring('www.csdn.net',-8,4) from web_info w;
+ -----------------------------------+
| substring('www.csdn.net',-8,4) |
+ -----------------------------------+
| csdn |
+ -----------------------------------+
1 row in set (0.00 sec)
Or write in the SQL standard way, the SQL is as follows:
mysql> SELECT substring('www.csdn.net' FROM 5 FOR 4) from web_info w;
+ ----------------------------------------+
| substring('www.csdn.net' FROM 5 FOR 4) |
+ ----------------------------------------+
| csdn |
+ ----------------------------------------+
1 row in set (0.00 sec)
In addition, mid() and substr() in MySQL are equivalent to the substring() function!
Fourth, SUBSTRING_INDEX() function
SUBSTRING_INDEX(str,delim,count) is a function for intercepting substrings through a specific identifier "delim", which is frequently used in our daily life;
- delim: intercepted by this identifier, delim can be any character, not empty;
- count: represents the number of occurrences; count is a positive number, which means to take the substring before the count occurrence of the identifier; negative number is the opposite, and takes the substring after the count occurrence of the identifier.
For example: 'www.csdn.net', get the substring before the first occurrence of the identifier '.', the SQL is as follows;
mysql> SELECT SUBSTRING_INDEX('www.csdn.net','.',1) from web_info w;
+ ------------------------------------------+
| SUBSTRING_INDEX('www.csdn.net','.',1) |
+ ------------------------------------------+
| www |
+ ------------------------------------------+
1 row in set (0.00 sec)
Get the substring after the first occurrence of the identifier '.', the SQL is as follows;
mysql> SELECT SUBSTRING_INDEX('www.csdn.net','.',-2) from web_info w;
+ ----------------------------------------+
| SUBSTRING_INDEX('www.csdn.net','.',-2) |
+ ----------------------------------------+
| csdn.net |
+ ----------------------------------------+
1 row in set (0.00 sec)
Want to get the middle of two '.'? It can be nested, the SQL is as follows;
mysql> SELECT SUBSTRING_INDEX(substring_index('www.csdn.net','.',2),'.',-1) from web_info w;
+ ------------------------------------------------- -------------- +
| SUBSTRING_INDEX(substring_index('www.csdn.net','.',2),'.',-1) |
+ ------------------------------------------------- -------------- +
| csdn |
+ ------------------------------------------------- -------------- +
1 row in set (0.00 sec)
5. Actual combat operation
We use the table web_info as the test table, and the data is as follows;
mysql> select * from web_info; + ------ + -------- + ---------------- + ---------------- ----- + | w_id | w_name | w_domain | pub_time | + ------ + -------- + ---------------- + ---------------- ----- + | 1 | CSDN | www.csdn.net | 2020-09-03 11:29:29 | | 5 | Baidu | www.baidu.com | 2020-09-18 14:37:38 | | 6 | Taobao | www.taobao.com | 2020-09-03 14:37:57 | + ------ + -------- + ---------------- + ---------------- ----- + 3 rows in set (0.00 sec)
Requirement 1: Obtain the first-level domain name of web_info data;
Note: This is only for example, and there may be some discrepancies with the actual situation. For example, multi-level suffixes like .com.cn need to be handled separately.
mysql> SELECT SUBSTRING_INDEX(w_domain,'.',-2),w.* from web_info w; + ---------------------------------- + ------ + ------- - + ---------------- + --------------------- + | SUBSTRING_INDEX(w_domain,'.',-2) | w_id | w_name | w_domain | pub_time | + ---------------------------------- + ------ + ------- - + ---------------- + --------------------- + | csdn.net | 1 | CSDN | www.csdn.net | 2020-09-03 11:29:29 | | baidu.com | 5 | Baidu | www.baidu.com | 2020-09-18 14:37:38 | | taobao.com | 6 | Taobao | www.taobao.com | 2020-09-03 14:37:57 | + ---------------------------------- + ------ + ------- - + ---------------- + --------------------- + 3 rows in set (0.00 sec)
Requirement 2: Change the data in the w_domain field to a first-level domain name;
update web_info set w_domain = SUBSTRING_INDEX(w_domain,'.',-2) ;
Modified query:
mysql> SELECT * from web_info; + ------ + -------- + ------------ + ----------------- - + | w_id | w_name | w_domain | pub_time | + ------ + -------- + ------------ + ----------------- - + | 1 | CSDN | csdn.net | 2020-09-03 14:54:59 | | 5 | Baidu | baidu.com | 2020-09-03 14:54:59 | | 6 | Taobao | taobao.com | 2020-09-03 14:54:59 | + ------ + -------- + ------------ + ----------------- - + 3 rows in set (0.00 sec)