How the SUBSTRING_INDEX() function works in Mariadb?

The SUBSTRING_INDEX() function in MariaDB is used to return a substring from a given string, up to a specified delimiter. It allows you to extract a part of the string before or after the specified delimiter, based on the count parameter. This function is particularly useful when you need to split or parse strings containing delimiters.

Syntax

The syntax for the MariaDB SUBSTRING_INDEX() function is as follows:

SUBSTRING_INDEX(str, delim, count)

• str: The input string from which the substring needs to be extracted. This is a required parameter.
• delim: The delimiter string that separates the substrings within the input string. This is a required parameter.
• count: An integer value that determines which substring to return. If count is positive, the function returns the substring before the countth occurrence of the delimiter. If count is negative, the function returns the substring after the countth occurrence of the delimiter, counting from the end of the string. This is a required parameter.

The function returns the substring based on the specified delimiter and count. If the delimiter is not found, it returns the entire string.

Examples

Example 1: Extracting a substring before a delimiter

This example demonstrates how to extract a substring before a specified delimiter.

SELECT SUBSTRING_INDEX('www.example.com', '.', 2) AS result;

The following is the output:

+-------------+
| result      |
+-------------+
| www.example |
+-------------+

In this example, the SUBSTRING_INDEX() function returns the substring before the second occurrence of the delimiter '.' in the input string 'www.example.com'. The resulting substring is 'www.example'.

Example 2: Extracting a substring after a delimiter

This example shows how to extract a substring after a specified delimiter.

SELECT SUBSTRING_INDEX('[email protected]', '@', -1) AS result;

The following is the output:

+-------------+
| result      |
+-------------+
| example.com |
+-------------+

In this example, the SUBSTRING_INDEX() function returns the substring after the last occurrence of the delimiter '@' in the input string '[email protected]'. The resulting substring is 'example.com'.

Example 3: Extracting a substring from a delimited string

The SUBSTRING_INDEX() function can be used to extract a substring from a delimited string, such as a comma-separated list.

DROP TABLE IF EXISTS example;
CREATE TABLE example (names VARCHAR(100));
INSERT INTO example (names) VALUES ('John,Jane,Bob,Alice');

SELECT names, SUBSTRING_INDEX(names, ',', 2) AS result
FROM example;

The following is the output:

+---------------------+-----------+
| names               | result    |
+---------------------+-----------+
| John,Jane,Bob,Alice | John,Jane |
+---------------------+-----------+

In this example, the SUBSTRING_INDEX() function extracts the substring before the third occurrence of the delimiter ',' in the names column. The resulting substring is 'John,Jane'.

Example 4: Handling NULL values

If the input string is NULL, the SUBSTRING_INDEX() function returns NULL.

DROP TABLE IF EXISTS example;
CREATE TABLE example (text VARCHAR(100));
INSERT INTO example (text) VALUES ('Hello, World!'), (NULL);

SELECT text, SUBSTRING_INDEX(text, ' ', 1) AS result
FROM example;

The following is the output:

+---------------+--------+
| text          | result |
+---------------+--------+
| Hello, World! | Hello, |
| NULL          | NULL   |
+---------------+--------+

In this example, the second row returns NULL because the text column contains a NULL value.

Example 5: Using SUBSTRING_INDEX() in combination with other functions

The SUBSTRING_INDEX() function can be used in combination with other string functions to perform more complex operations.

SELECT SUBSTRING_INDEX('[email protected]', '@', 1) AS username,
       SUBSTRING_INDEX('[email protected]', '@', -1) AS domain;

The following is the output:

+----------+-------------+
| username | domain      |
+----------+-------------+
| john.doe | example.com |
+----------+-------------+

In this example, the SUBSTRING_INDEX() function is used twice to extract the username and domain parts from an email address. The first call returns the substring before the '@' delimiter, and the second call returns the substring after the '@' delimiter.

Related Functions

The following are some functions related to the MariaDB SUBSTRING_INDEX() function:

• MariaDB SUBSTR() function is used to extract a substring from a string based on position and length.
• MariaDB INSTR() function is used to find the position of a substring within a string.
• MariaDB REPLACE() function is used to replace occurrences of a substring within a string with a new substring.

Conclusion

The SUBSTRING_INDEX() function in MariaDB is a powerful tool for extracting substrings from a string based on a specified delimiter. It allows you to extract the substring before or after a particular occurrence of the delimiter, making it useful for parsing and manipulating delimited strings. By understanding the syntax and usage of this function, you can efficiently split and extract data from strings in your SQL queries and applications.