MySQL string intercept function and usage detailed explanation

Detailed explanation of MySQL string intercept function

In MySQL, string interception is a common operation, mainly used to extract specific parts from strings. MySQL provides a variety of functions to implement this function, includingLEFT()、RIGHT()、SUBSTRING()、MID()、SUBSTR()andSUBSTRING_INDEX()wait. This article will introduce the usage of these functions in detail and explain them with examples.

LEFT(str, length): Intercept characters of the specified length from the left

Function: From a stringstrStart on the left side, interceptlengthcharacters.

grammar：

LEFT(str, length)

Example：

SELECT LEFT('', 3);

result：

+-------------------------+
| LEFT('', 3) |
+-------------------------+
| sql |
+-------------------------+

explain:from''Start on the left side of the 3 characters, and the result is'sql'。

RIGHT(str, length): Intercept characters of the specified length from the right

Function: From a stringstrStart on the right side, interceptlengthcharacters.

grammar：

RIGHT(str, length)

Example：

SELECT RIGHT('', 3);

result：

+--------------------------+
| RIGHT('', 3) |
+--------------------------+
| com |
+--------------------------+

explain:from''Start on the right side of the 3 characters, and the result is'com'。

SUBSTRING(str, pos) or SUBSTRING(str, pos, len): starts to intercept from the specified location

Function: From a stringstrThe firstposStart with characters, interceptlencharacters.posIt can be a positive or negative number, with a positive number starting from the left and a negative number starting from the right.

grammar：

SUBSTRING(str, pos)
SUBSTRING(str, pos, len)

Example：

-- From 4 Start with characters，Until the end
SELECT SUBSTRING('', 4);
-- From 4 Start with characters，Intercept 2 Characters
SELECT SUBSTRING('', 4, 2);
-- From the last 4 Start with characters，Until the end
SELECT SUBSTRING('', -4);
-- From the last 4 Start with characters，Intercept 2 Characters
SELECT SUBSTRING('', -4, 2);

result：

+------------------------------+
| SUBSTRING('', 4) |
+------------------------------+
| |
+------------------------------+

+---------------------------------+
| SUBSTRING('', 4, 2) |
+---------------------------------+
| st |
+---------------------------------+

+-------------------------------+
| SUBSTRING('', -4) |
+-------------------------------+
| .com |
+-------------------------------+

+----------------------------------+
| SUBSTRING('', -4, 2) |
+----------------------------------+
| .c |
+----------------------------------+

explain：

• SUBSTRING('', 4): Starting from the 4th character until the end of the string, the result is''。
• SUBSTRING('', 4, 2): Starting from the 4th character, 2 characters are intercepted, and the result is'st'。
• SUBSTRING('', -4): Starting from the fourth last character until the end of the string, the result is'.com'。
• SUBSTRING('', -4, 2): Starting from the fourth last character, 2 characters are intercepted, and the result is'.c'。

Notice：

lenMust be non-negative integer. If omittedlen, then the end of the string is intercepted by default.

likeposExceed the string length or is 0, return an empty string. For example:

SELECT SUBSTRING('abc', 0, 1);   -- result：Empty string
SELECT SUBSTRING('abc', 5, 1);   -- result：Empty string

SUBSTRING_INDEX(str, delim, count): Intercept strings according to delimiter

Function: According to the separatordelim, from stringstrInsert the secondcountSubstring before or after the separator that appears.countWhen it is a positive number, count starts from the left; when it is a negative number, count starts from the right.

grammar：

SUBSTRING_INDEX(str, delim, count)

Example：

-- Intercept the second one '.' All previous characters
SELECT SUBSTRING_INDEX('', '.', 2);
 -- Intercept the penultimate one '.' All characters after
SELECT SUBSTRING_INDEX('', '.', -2);
-- If the delimiter does not exist，Return the entire string
SELECT SUBSTRING_INDEX('', '.coc', 1);

result：

+------------------------------------------------+
| SUBSTRING_INDEX('', '.', 2) |
+------------------------------------------------+
| |
+------------------------------------------------+

+-------------------------------------------------+
| SUBSTRING_INDEX('', '.', -2) |
+-------------------------------------------------+
| |
+-------------------------------------------------+

+---------------------------------------------------+
| SUBSTRING_INDEX('', '.coc', 1) |
+---------------------------------------------------+
| |
+---------------------------------------------------+

explain：

• SUBSTRING_INDEX('', '.', 2): Return to the second time it appears.All previous characters, the result is''。
• SUBSTRING_INDEX('', '.', -2): Return to the second time that appeared last.All characters after that result is''。
• SUBSTRING_INDEX('', '.coc', 1):because.cocDoes not exist in a string, return the entire string''。

Notice：

likecountThe number of times the delimiter actually appears is exceeded, and the entire string is returned. For example:

SELECT SUBSTRING_INDEX('', '.', 5); -- Result:''

MID(str, pos, len) or SUBSTR(str, pos, len): withSUBSTRINGequivalence

Function:andSUBSTRINGThe function is the same and is used to intercept substrings of a specified length from the specified position.

grammar：

MID(str, pos, len)
SUBSTR(str, pos, len)

Example：

-- From 4 Start with characters，Intercept 2 Characters
SELECT MID('', 4, 2);
-- From the last 4 Start with characters，Intercept 2 Characters
SELECT SUBSTR('', -4, 2);

result：

+----------------------------------+
| MID('', 4, 2) |
+----------------------------------+
| st |
+----------------------------------+

+-----------------------------------+
| SUBSTR('', -4, 2) |
+-----------------------------------+
| .c |
+-----------------------------------+

explain：

• MID('', 4, 2)andSUBSTRING('', 4, 2)The results are the same, both'st'。
• SUBSTR('', -4, 2)andSUBSTRING('', -4, 2)The results are the same, both'.c'。

Multi-byte character processing

MySQL's string intercept function is handled by character (rather than bytes) by default and is suitable for multibyte character sets (such as UTF-8). For example:

SELECT LEFT('Chinese test', 2);  -- result：'Chinese'
SELECT SUBSTRING('database', 2, 2);  -- result：'Domain'

Practical application scenarios

Scenario 1: Extract the domain name of the email address

SELECT 
  email,
  SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

result：

+-------------------+-------------+
| email | domain |
+-------------------+-------------+
| user@ | |
+-------------------+-------------+

Scenario 2: Parsing the path in the URL

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(url, '//', -1), '/', 1) AS host
FROM website;

enter：'/path'
Output：''

Summarize

Mastering these functions can efficiently handle string interception requirements and improve data processing flexibility.