SUBSTRING

Description

The SUBSTRING function is used to extract a substring from a string or binary data. Based on the specified starting position (pos) and length (len), the SUBSTRING function returns the corresponding substring.

Syntax

substring(str, pos [, len])
    
substring(str FROM pos [FOR len])

Parameters

• str (string/binary): The input string or binary data.
• pos (bigint): The starting position of the substring. If pos is greater than or equal to 1, it starts from the pos-th character from the left; if pos is less than or equal to -1, it starts from the -pos-th character from the right; if pos is equal to 0, it starts from the first character on the left.
• len (bigint, optional): The length of the substring. If not specified, the complete substring starting from pos is returned.

Return Result

Returns a string representing the substring extracted from the input string.

Usage Example

1. Extract the first two characters of the string:

   > SELECT substring('Hello, world!', 1, 2);
   He
   

2. Extract the complete substring starting from the fourth character:

   > SELECT substring('Hello, world!', 4);
   lo, world!
   

3. Extract the two characters starting from the second to last character of the string:

   > SELECT substring('Hello, world!', -2, 2);
   d!
   

4. Extract the domain name from the URL:

   > SELECT substring('http\://www\.example.com', LOCATE('://', 'http\://www\.example.com')+3);
   www.example.com
   

5. Extract specific parts of a string (e.g., extract month and date):

   > SELECT substring('2023-04-15', 5, 5);
   -04-1
   

By the above examples, you can see the application of the SUBSTRING function in different scenarios. Using the SUBSTRING function allows you to conveniently extract the required substring from a string, thereby meeting various data processing needs.