MySQL's SUBSTRING function is used to extract substrings from strings. Its basic syntax is SUBSTRING(str, start, length) or SUBSTRING(str FROM start FOR length), where str is the original string, start is the starting position (starting from 1), and length is the number of characters to be extracted (optional). 1. If length is omitted during use, it will be extracted to the end of the string; 2. It is often used to extract specific parts from fields, such as extracting user IDs in mailbox domain names or paths; 3. Note that the index starts from 1, start returns an empty string beyond the length, and calculates by characters rather than bytes when processing multi-byte characters; 4. It can be used in combination with other functions such as LOCATE(), CHAR_LENGTH(), etc. to achieve more complex string processing tasks. Mastering these key points can help to process text data efficiently.

MySQL's SUBSTRING function can be used to extract substrings from strings. It is a very practical function when processing text data. In practical applications, such as intercepting usernames, resolving address information, and extracting log content, they will be used.

1. Basic syntax and usage

There are several forms of basic writing of SUBSTRING function:

• SUBSTRING(str, start, length)
• SUBSTRING(str FROM start FOR length)

in:

• str is the original string
• start is the starting position (counting from 1)
• length is the number of characters to be extracted (optional)

For example, if you want to extract 'World' from 'Hello World' , you can write it like this:

 SELECT SUBSTRING('Hello World', 7, 5);

The result is 'World' . Note that spaces are also considered as a character, so 'H' is the first bit, the spaces followed are the 6th bit, and 'W' is the 7th bit.

If length is omitted, it will be taken to the end of the string:

 SELECT SUBSTRING('Hello World', 7); -- Get 'World'

2. Use scenario: Extract specific parts from fields

In actual development, we often need to extract part of the content from a field in the database table. For example, there is a users table with a field called email . You want to extract the domain name part of each user:

 SELECT SUBSTRING(email FROM LOCATE('@', email) 1) AS domain FROM users;

Here, LOCATE() function is combined to find the location of @ , and then the entire domain name is extracted from its next bit.

For example, you have a column of record paths such as /user/12345/profile.jpg and want to extract the user ID part:

 SELECT SUBSTRING(path, 7, 5) FROM logs;

As long as you know the fixed format, you can quickly extract it in this way.

3. Precautions and FAQs

There are several details that are prone to errors when using SUBSTRING , and you need to pay attention to:

• The index starts at 1 , not 0. If you are used to programming language array indexing may be confused.
• If start exceeds the string length, an empty string is returned.
• If length is negative or 0, it may also result in a return of null values or errors, depending on the version and configuration.
• For multi-byte characters (such as Chinese), pay attention to the influence of the character set. For example, under utf8mb4 , a Chinese character may occupy 4 bytes, but SUBSTRING is calculated by characters rather than bytes.

For example:

 SELECT SUBSTRING('Hello World', 3, 2); -- Return to 'World'

Because "you" is the first character, "good" is the second, and "world" is the third, so taking 2 characters from the third is "world".

4. It is more powerful when paired with other string functions

SUBSTRING is often used with the following functions:

• LOCATE() : Find the location of a character
• CHAR_LENGTH() : Get the character length (note that it is not LENGTH() , that is the byte length)
• LEFT() / RIGHT() : Start taking characters from the left or right (supported by MySQL 5.7)
• SUBSTR() : It is actually an alias for SUBSTRING and can be used interchangeably.

For example, you want to extract the content in brackets from a log:

 SELECT 
  SUBSTRING(log_message FROM LOCATE('[', log_message) 1 FOR LOCATE(']', log_message) - LOCATE('[', log_message) - 1)
FROM logs;

This statement seems a bit complicated, but it explains how to combine multiple functions to accomplish a more precise extraction task.

Basically that's it. Mastering the basic usage and common combinations of SUBSTRING will be much more convenient when processing strings. Although the function is simple, it can solve many practical problems if used cleverly.

The above is the detailed content of mysql substring function. For more information, please follow other related articles on the PHP Chinese website!