Specific use of MySQL segmentation function substring()

There are four main MySQL string interception functions: left(), right(), substring(), and substring_index(). Each has its own usage scenario. Let me take a few minutes to get you familiar with them today, Mark!

聲明一下：在MySQL中，下標索引是從1開始的，而不是像java中從0開始的喔！

insert image description here

1. LEFT() function

LEFT(string,length) , starting from the first character on the left of string, extract length characters. length should be greater than 0. If <= 0, an empty string is returned. Here is an example:

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)

2. RIGHT() function

LEFT(string,length) , starting from the first character on the right of string, extract length characters, still in forward order. length should be greater than 0. If <= 0, an empty string is returned.

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 specifically used to split a string. It has two main forms:

SUBSTRING(string,position);
SUBSTRING(string,position,length);

The following is the SQL standard version of the above two types of statements, which means the same thing. It is longer but more expressive.

SUBSTRING(string FROM position);
SUBSTRING(string FROM position FOR length);

Let's look at two examples of how SUBSTRING() can be used;

1.SUBSTRING(string,position)

string : The parameter is the string to be operated on.
position : The parameter is an "整數" used to specify the starting character of the substring. Position can be a positive or negative integer.若position大于操作字符串的長度，則返回空字符串。

For example, to get the substring "csdn.net" from the string "www.csdn.net", the position of the substring must start from 5, as shown 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是一個正整數that specifies the number of characters in the substring.如果length<=0，那么會返回空字符串。

For example, to obtain the 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 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 you can write it in the standard SQL 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!

4. SUBSTRING_INDEX() function

SUBSTRING_INDEX(str,delim,count) is a function that extracts a substring by a specific identifier "delim". We use it frequently in daily life.

delim : interception is done through this identifier, delim can be any character and cannot be empty;
count : represents the number of occurrences; if count is a positive number, it means taking the substring before the identifier appears count times; if it is a negative number, it means taking the substring after the identifier appears count times.

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)

insert image description here

5. Practical 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: Get the first-level domain name of web_info data;
Note: This is only used as an example and may differ from the actual situation. For example, multi-level suffixes such as .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 w_domain field data to the 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)

Well, that’s all for MySQL’s string splitting function today. If it’s useful to you, please don’t share it for free~~

This is the end of this article about the specific use of the MySQL segmentation function substring(). For more relevant MySQL segmentation function substring content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: