Mysql string interception and obtaining data in the specified string

Mysql string interception and obtaining data in the specified string

Preface: I encountered a requirement to extract specific characters from a string in a MySql field, similar to the interception of regular expressions. I had no suitable method. After searching on Baidu, I finally found a suitable method: substring_index('www.sqlstudy.com.cn', '.', -2)

This method is highly recommended to retrieve data containing specific characters.

substring_index(input,split,index): input is the character to be intercepted, split is the delimiter, and Index is the string to be intercepted to the left (index is positive) or right (index is negative) of the index-th delimiter.

Take a personal string as an example: 'Provider="RiskManagement" finalScore="65" RGID="100397278"' I want to get the value of finalScore:

-- 1-Get the characters to the right of finalScore select substring_index('Provider="RiskManagement" finalScore="65" RGID="100397278"','finalScore="',-1);

-- 2- Get the characters on the left of "RGID=" select substring_index(substring_index('Provider="RiskManagement" finalScore="65" RGID="100397278"','finalScore="',-1),'" RGID="',1);

Result 1: 65" RGID="100397278"

Result 2: 65

MySQL string extraction functions: left(), right(), substring(), substring_index(). There are also mid() and substr(). Among them, mid() and substr() are equivalent to the substring() function. The function of substring() is very powerful and flexible.

1. String truncation: left(str, length)

mysql> select left('sqlstudy.com', 3); 
+-------------------------+ 
| left('sqlstudy.com', 3) | 
+-------------------------+ 
| sql | 
+-------------------------+

2. String interception: right(str, length)

mysql> select right('sqlstudy.com', 3); 
+--------------------------+ 
| right('sqlstudy.com', 3) | 
+--------------------------+ 
| com | 
+--------------------------+

3. String extraction: substring(str, pos); substring(str, pos, len)

3.1 Start from the 4th character position of the string and continue until the end.

mysql> select substring('sqlstudy.com', 4); 
+------------------------------+ 
| substring('sqlstudy.com', 4) | 
+------------------------------+ 
| study.com | 
+------------------------------+

3.2 Start from the 4th character position of the string and take only 2 characters.

mysql> select substring('sqlstudy.com', 4, 2); 
+---------------------------------+ 
| substring('sqlstudy.com', 4, 2) | 
+---------------------------------+ 
| st | 
+---------------------------------+

3.3 Start from the 4th character position (counting backwards) of the string and continue until the end.

mysql> select substring('sqlstudy.com', -4); 
+-------------------------------+ 
| substring('sqlstudy.com', -4) | 
+-------------------------------+ 
| .com | 
+-------------------------------+

3.4 Start from the 4th character position (counting backwards) of the string and take only 2 characters.

mysql> select substring('sqlstudy.com', -4, 2); 
+----------------------------------+ 
| substring('sqlstudy.com', -4, 2) | 
+----------------------------------+ 
| .c | 
+----------------------------------+

We notice that in the function substring(str,pos,len), pos can be negative, but len ​​cannot be negative.

4. String extraction: substring_index(str,delim,count)

4.1 Intercept all characters before the second '.'

mysql> select substring_index('www.sqlstudy.com.cn', '.', 2); 
+------------------------------------------------+ 
| substring_index('www.sqlstudy.com.cn', '.', 2) | 
+------------------------------------------------+ 
| www.sqlstudy | 
+------------------------------------------------+


4.2 Intercept all characters after the second '.' (the last character).

mysql> select substring_index('www.sqlstudy.com.cn', '.', -2); 
+-------------------------------------------------+ 
| substring_index('www.sqlstudy.com.cn', '.', -2) | 
+-------------------------------------------------+ 
| com.cn | 
+-------------------------------------------------+


4.3 If the value specified by the delim parameter is not found in the string, the entire string is returned

mysql> select substring_index('www.sqlstudy.com.cn', '.coc', 1); 
+---------------------------------------------------+ 
| substring_index('www.sqlstudy.com.cn', '.coc', 1) | 
+---------------------------------------------------+ 
| www.sqlstudy.com.cn | 
+---------------------------------------------------+

4.4 Extract the middle value of a field in a table, such as 1, 2, 3

mysql> select substring_index(substring_index(the field, ',', 2), ',', -1) from table name; 
+--------------------------------------------------------------+ 
| substring_index(substring_index(the field, ',', 2); , ',', -1)| 
+--------------------------------------------------------------+ 
| 2 | 
+--------------------------------------------------------------+

Summarize

The above is what I introduced to you about Mysql string interception and obtaining data in the specified string. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:
  • Usage instructions for the Mysql string interception function SUBSTRING
  • MySQL interception and split string function usage examples
  • mysql intercepts the content between two specified strings
  • Usage of MySQL intercepted string function substring_index
  • MySQL intercepts the sql statement of the string function
  • Summary of MySQL string interception related functions
  • MySQL implements string interception graphic tutorial

<<:  Docker deployment of Flask application implementation steps

>>:  How to use the vue timeline component

Recommend

Introduction to the use of html base tag target=_parent

The <base> tag specifies the default address...

Detailed explanation of the implementation of nginx process lock

Table of contents 1. The role of nginx process lo...

MySQL optimization strategy (recommended)

In summary: 1. Consider performance when designin...

How to quickly paginate MySQL data volumes of tens of millions

Preface In backend development, in order to preve...

CentOS 7 configuration Tomcat9+MySQL solution

Configure Tomcat First install Tomcat Installing ...

Example of using setInterval function in React

This article is based on the Windows 10 system en...

Getting Started Tutorial for Beginners⑧: Easily Create an Article Site

In my last post I talked about how to make a web p...

Vue.$set failure pitfall discovery and solution

I accidentally found that Vue.$set was invalid in...

css input[type=file] style beautification (input upload file style)

Effect: <!doctype html> <html> <he...

19 MySQL optimization methods in database management

After MySQL database optimization, not only can t...

Docker View the Mount Directory Operation of the Container

Only display Docker container mount directory inf...

The iframe child page operates the parent page and implements the effect of shielding the page pop-up layer

Question: In index.html, iframe introduces son.htm...

Get the calculated style in the CSS element (after cascading/final style)

To obtain the calculated style in a CSS element (t...

Detailed tutorial on MySQL installation and configuration

Table of contents Installation-free version of My...

Detailed explanation of Vue3.0 + TypeScript + Vite first experience

Table of contents Project Creation Project Struct...