What are the methods for implementing string interception in MySQL?

Preface

It is common to deal with the database during the back-end development process. Sometimes you will encounter only a part of a certain piece of data in the database. In this case, there are two ways:

(1) Read it out and then process it accordingly

(2) Use the functions that come with MySQL for processing when reading

Next we will use the second There are three ways to summarize columns

(1) Create a user table as follows:

The LEFT(col_name, length) function: extracts from left to right. col_name is the column name (required), length is the length taken from left to right (required to be a positive integer, if it is a negative number, nothing is returned, the subscript starts from 1, not 0)

A : length: integer

B: length: negative number

##The function right(col_name, length) extracts characters from the right-hand side of the string. col_name is the column name (required), length is the length taken from right to left (must be a positive integer, if it is a negative number, nothing is returned. The subscript starts from 1, not 0)

A: length : Integer

B: length: Negative number

## (4) substring(col_name, start, length) function: col_name column name (required), start from which number (required to be an integer starting from 1), length truncation length (optional, positive integer)

A: start: positive integer, length If not selected, counting from left to right, starting from the third character and cutting to the right until the end.

B: start: negative integer, length is not selected, counting from the right to the left, starting from the third character and intercepting it to the right until the end.

C: start: positive integer, length: positive integer; counting from left to right, 5 characters are intercepted from the third character to the right.

D: start: negative integer, length: positive integer; counting from the right to the left, starting from the third character, 3 characters are intercepted to the right.

E: When intercepting from star and the number of characters immediately following it

F: start: negative integer, length: negative integer; nothing is intercepted

(5) substring_index(col, a, num) function: from the Use num a's to divide col. When num is a positive integer, it is divided from left to right and taken from left to right; when num is a negative integer, it is divided from right to left and taken from right to left.

A: Split by the first "o" of hello world (after splitting: hell oworld)

B: Split by the first "o" of hello world The second ‘o’ is used for segmentation (after segmentation: hello w orld)

C: Counting from right to left, use the first ‘o’ Split (after splitting: hello wo rld)

D: Counting from right to left, split with the second ‘o’ (after splitting: hello world)

Attachment: Detailed explanation of functions related to mysql string interception

1. SUBSTRING_INDEX(subStr, matchStr, index)

Start intercepting the string based on the matching characters and their occurrence positions

subStr: Fields to be intercepted
matchStr: Matching characters

• index: Starting from the matching character, if it is a positive number, search from the left and intercept to the left; if it is a negative number, search from the right and intercept to the right

2. LEFT(subStr,index)

Start from the left and intercept it to the right, ending at the index position (index starts from 1)

• subStr: The field that needs to be intercepted

• index: The position to stop interception (including the characters at this position)

3. RIGHT(subStr,index)

Start from the right and intercept to the left, ending at the index position from the right (index starts from 1)

• subStr: The field that needs to be intercepted

• index: The position to stop interception (including the characters at this position)

4, SUBSTRING( subStr, index)

When index is a negative number, start from the right and intercept to the left, up to the index position from the right

When index is a positive number, start from the left Intercept to the right, up to the index position from the left

• subStr: The field to be intercepted

• index: The position to stop interception (including characters at this position)

The above is the detailed content of What are the methods for implementing string interception in MySQL?. For more information, please follow other related articles on the PHP Chinese website!