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

Recommended 20 best free English handwriting fonts

Jellyka BeesAntique Handwriting [ank]* Jellyka Cut...

CSS Sticky Footer Several Implementations

What is "Sticky Footer" The so-called &...

Why can't I see the access interface for Docker Tomcat?

Question: Is the origin server unable to find a r...

Detailed explanation of the JavaScript timer principle

Table of contents 1. setTimeout() timer 2. Stop t...

Solution to the problem of text position jumping when the search text box leaves the focus

When setting the text in the search text box, the...

WeChat applet implements waterfall flow paging scrolling loading

This article shares the specific code for WeChat ...

Detailed explanation of Vue configuration request multiple server solutions

1. Solution 1.1 Describing the interface context-...

Web Design Tutorial (7): Improving Web Design Efficiency

<br />Previous article: Web Design Tutorial ...

JS implements random roll call system

Use JS to implement a random roll call system for...

HTML simple web form creation example introduction

<input> is used to collect user information ...

Setting z-index property for flash overlay popup layer in web design does not work

By default, Flash will always be displayed at the ...

Ajax jquery realizes the refresh effect of a div on the page

The original code is this: <div class='con...

mysql 5.7.23 winx64 decompression version installation tutorial

Detailed installation tutorial of mysql-5.7.23-wi...

Multiple ways to change the SELECT options in an HTML drop-down box

After the form is submitted, the returned HTML pag...

Introduction to the process of extending the boot partition in Kylin 4.0.2 (Ubuntu)

Table of contents Preface 1. Prepare new partitio...