SUBSTRING_INDEX

Extracts the portion of a string up to the given number of occurrences of a delimiter.

This is mainly useful for delimited strings, such as a CSV or ASCII table. If the count argument is negative, the delimiters will be counted starting from the right, and the portion of the string to the right of the final delimiter will be returned.

Syntax

SUBSTRING_INDEX(str, delimiter, count)

Arguments

  • str: any string or binary object

  • delimiter: the field delimiter

  • count: the number of fields to extract

Return Type

String

Examples

SELECT SUBSTRING_INDEX('a b c d', ' ', 3);
+------------------------------------+
| SUBSTRING_INDEX('a b c d', ' ', 3) |
+------------------------------------+
| a b c                              |
+------------------------------------+
SELECT SUBSTRING_INDEX('de305d54-75b4-431b-adb2-eb613', '-', 2) as uuid;
+---------------+
| uuid          |
+---------------+
| de305d54-75b4 |
+---------------+
SELECT SUBSTRING_INDEX('01-23-45-67-89', '-', -2);
+--------------------------------------------+
| SUBSTRING_INDEX('01-23-45-67-89', '-', -2) |
+--------------------------------------------+
| 67-89                                      |
+--------------------------------------------+

Last modified: February 27, 2023

Was this article helpful?