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

WeChat applet learning wxs usage tutorial

What is wxs? wxs (WeiXin Script) is a scripting l...

Two ways to reset the root password of MySQL database using lnmp

The first method: Use Junge's one-click scrip...

Implementing carousel effects with JavaScript

This article shares the specific code for JavaScr...

VMware Workstation 12 Pro Linux installation tutorial

This article records the VMware Workstation 12 Pr...

Detailed explanation of the execution plan explain command example in MySQL

Preface The explain command is the primary way to...

A brief analysis of the count tracking of a request in nginx

First, let me explain the application method. The...

Vue3 compilation process-source code analysis

Preface: Vue3 has been released for a long time. ...

Solve the problem of MySQL 8.0 still prompting Access denied even though the input is correct

I started learning MySQL recently. The installati...

How to limit access frequency, download rate and number of concurrent connections in Nginx

1. Overview of modules and instructions used to l...

Vue3 gets the current routing address

Correct answer Using useRouter : // router path: ...

How to import and export Docker images

This article introduces the import and export of ...

Vuex implements simple shopping cart function

This article example shares the specific code of ...

js implements some functions of the input component in Element and encapsulates it into a component (example code)

Currently implemented are basic usage, clearable,...

A summary of some of the places where I spent time on TypeScript

Record some of the places where you spent time on...

Linux command line operation Baidu cloud upload and download files

Table of contents 0. Background 1. Installation 2...