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

CSS web page responsive layout to automatically adapt to PC/Pad/Phone devices

Preface There are many devices nowadays, includin...

Use of Linux chkconfig command

1. Command Introduction The chkconfig command is ...

WeChat applet development form validation WxValidate usage

I personally feel that the development framework ...

A brief discussion on MySQL count of rows

We are all familiar with the MySQL count() functi...

Introduction and tips for using the interactive visualization JS library gojs

Table of contents 1. Introduction to gojs 2. Gojs...

0.1 seconds worth! A brief discussion on the problem of speeding up the front-end web pages

I remember when I was interviewing for my current...

How to deploy DoNetCore to Alibaba Cloud with Nginx

Basic environment configuration Please purchase t...

Detailed explanation of when javascript scripts will be executed

JavaScript scripts can be embedded anywhere in HT...

Analysis of the principle of MySQL large table extraction mode from the perspective of cloud data migration service

Summary: What method should be used for MySQL JDB...

7 ways to vertically center elements with CSS

【1】Know the width and height of the centered elem...

The front-end must know how to lazy load images (three methods)

Table of contents 1. What is lazy loading? 2. Imp...

CSS realizes the layout method of fixed left and adaptive right

1. Floating layout 1. Let the fixed width div flo...

WeChat applet canvas implements signature function

In the WeChat applet project, the development mod...

Implementation of remote Linux development using vscode

Say goodbye to the past Before vscode had remote ...

Detailed explanation of the use principle and calculation method of the umask command under Linux

Table of contents umask Umask usage principle 1. ...