SUBSTRING

Description

The SUBSTRING function is used to extract a substring from a string. You can specify the starting position and length, supporting both forward and backward extraction. The position of the first character in the string is 1.

Alias

SUBSTR

Syntax

SUBSTRING(<str>, <pos> [, <len>])

Parameters

Parameter	Description
<str>	Source string. Type: VARCHAR
<pos>	Starting position, can be negative. Type: INT
<len>	Optional parameter, length to extract. Type: INT

Return Value

Returns VARCHAR type, representing the extracted substring.

Special cases:

• If any parameter is NULL, returns NULL
• If pos is 0, returns an empty string
• If pos is negative, counts from the end of the string backwards
• If pos exceeds the string length, returns an empty string
• If len is not specified, returns all characters from pos to the end of the string

Examples

1. Basic usage (specify starting position)

SELECT substring('abc1', 2);

+-----------------------------+
| substring('abc1', 2)        |
+-----------------------------+
| bc1                         |
+-----------------------------+

2. Using negative position

SELECT substring('abc1', -2);

+-----------------------------+
| substring('abc1', -2)       |
+-----------------------------+
| c1                          |
+-----------------------------+

3. Case when position is 0

SELECT substring('abc1', 0);

+----------------------+
| substring('abc1', 0) |
+----------------------+
|                      |
+----------------------+

4. Position exceeds string length

SELECT substring('abc1', 5);

+-----------------------------+
| substring('abc1', 5)        |
+-----------------------------+
|                             |
+-----------------------------+

5. Specifying length parameter

SELECT substring('abc1def', 2, 2);

+-----------------------------+
| substring('abc1def', 2, 2)  |
+-----------------------------+
| bc                          |
+-----------------------------+