Specific use of MySQL segmentation function substring()

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:
  • How to use MySQL common functions to process JSON
  • Analysis of common usage examples of MySQL process functions
  • Common usage tutorial of mysql_fetch_* functions in PHP operation of MySQL
  • Detailed explanation of Mysql function call optimization
  • Example tutorial on using the sum function in MySQL
  • Use of MySQL DATE_FORMAT function
  • Common functions of MySQL basics

<<:  Simple web page code used in NetEase blog

>>:  SVG button example code based on CSS animation

Blog    

Recommend

A brief discussion on the difference between Mysql primary key index and non-primary key index

Table of contents What is an index The difference...

How to delete a property of an object in JavaScript

1. delete delete is the only real way to remove a...

JavaScript example code to determine whether a file exists

1. Business Scenario I have been doing developmen...

A brief discussion on JavaScript shallow copy and deep copy

Table of contents 1. Direct assignment 2. Shallow...

CSS scroll-snap scroll event stop and element position detection implementation

1. Scroll Snap is a must-have skill for front-end...

Detailed explanation of tcpdump command examples in Linux

Preface To put it simply, tcpdump is a packet ana...

A brief discussion on whether MySQL can have a function similar to Oracle's nvl

Use ifnull instead of isnull isnull is used to de...

Detailed explanation of Nginx installation, SSL configuration and common commands under Centos7.x

1. Installation Install using yum ##Automatically...

Vue implements small notepad function

This article example shares the specific code of ...

How to create a flame effect using CSS

The main text starts below. 123WORDPRESS.COM Down...

Basic usage of custom directives in Vue

Table of contents Preface text 1. Global Registra...

How to configure NAS on Windows Server 2019

Preface This tutorial installs the latest version...

SQL Practice Exercise: Online Mall Database Product Category Data Operation

Online shopping mall database-product category da...

Analysis of the configuration process of installing mariadb based on docker

1. Installation Search the mariadb version to be ...

A collection of button hover border and background animations based on CSS properties

Heart Attributes opacity: .999 creates a stacking...