---

{ “title”: “SUBSTRING”, “language”: “en”, “description”: “The SUBSTRING function is used to extract a substring from a string. You can specify the starting position and length,” }

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

MID

Syntax

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

SUBSTRING(<str> FROM <pos> [FOR <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                          |
+-----------------------------+

6. Using from and for

SELECT substring('foobarbar' FROM 4 FOR 3);

+-------------------------------------+
| substring('foobarbar' FROM 4 FOR 3) |
+-------------------------------------+
| bar                                 |
+-------------------------------------+

7. Using from

SELECT substring('foobarbar' FROM 4);

+-------------------------------+
| substring('foobarbar' FROM 4) |
+-------------------------------+
| barbar                        |
+-------------------------------+

8. NULL example with alias MID

SELECT MID(NULL, 2);

+--------------+
| MID(NULL, 2) |
+--------------+
| NULL         |
+--------------+

9. Using alias SUBSTR

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

+------------------------------+
| SUBSTR('Hello World', 7, 5)  |
+------------------------------+
| World                        |
+------------------------------+

10. UTF-8 multi-byte characters

SELECT SUBSTRING('ṭṛì ḍḍumai hello', 5, 6);

+--------------------------------------+
| SUBSTRING('ṭṛì ḍḍumai hello', 5, 6)  |
+--------------------------------------+
| ḍḍumai                               |
+--------------------------------------+

Keywords

SUBSTRING, SUBSTR, MID