How the SUBSTRING() function works in Mariadb?

The SUBSTRING() function in MariaDB is used to extract a substring from a given string, based on the specified starting position and optional length. It allows you to retrieve a portion of a string, which is useful for various string manipulation tasks.

Syntax

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

SUBSTRING(str, pos, len)

• str: The input string from which the substring needs to be extracted. This is a required parameter.
• pos: The starting position of the substring within the input string. The position is counted from 1. This is a required parameter.
• len: The length of the substring to be extracted. If len is omitted, the function returns the substring starting from pos until the end of the string. This is an optional parameter.

The function returns a substring of the specified length, starting from the specified position within the input string. If the starting position is negative, the function treats it as counting from the end of the string. If the starting position or length is out of range, the function returns an empty string or a substring up to the end of the string.

Examples

Example 1: Extracting a substring from the middle of a string

This example demonstrates how to extract a substring from the middle of a given string.

SELECT SUBSTRING('Hello, World!', 8, 5) AS result;

The following is the output:

+--------+
| result |
+--------+
| World  |
+--------+

In this example, the SUBSTRING() function extracts a substring of length 5, starting from position 8 in the input string 'Hello, World!'. The resulting substring is 'World'.

Example 2: Extracting a substring from the beginning of a string

This example shows how to extract a substring from the beginning of a string.

SELECT SUBSTRING('MariaDB', 1, 6) AS result;

The following is the output:

+--------+
| result |
+--------+
| MariaD |
+--------+

In this example, the SUBSTRING() function extracts a substring of length 6, starting from position 1 in the input string 'MariaDB'. The resulting substring is 'MariaD'.

Example 3: Extracting a substring from the end of a string

The SUBSTRING() function can also extract a substring from the end of a string by using a negative position.

DROP TABLE IF EXISTS example;
CREATE TABLE example (name VARCHAR(50));
INSERT INTO example (name) VALUES ('John Doe'), ('Jane Smith');

SELECT name, SUBSTRING(name, -4, 3) AS result
FROM example;

The following is the output:

+------------+--------+
| name       | result |
+------------+--------+
| John Doe   |  Do    |
| Jane Smith | mit    |
+------------+--------+

In this example, the SUBSTRING() function extracts a substring of length 3, starting from position -4 (counting from the end of the string) in the name column. The resulting substrings are ' Do' and 'mit'.

Example 4: Extracting a substring without specifying length

If the length parameter is omitted, the SUBSTRING() function extracts the substring from the specified position until the end of the string.

SELECT SUBSTRING('Database', 5) AS result;

The following is the output:

+--------+
| result |
+--------+
| base   |
+--------+

In this example, the SUBSTRING() function extracts the substring starting from position 5 in the input string 'Database' until the end of the string, resulting in 'base'.

Example 5: Handling NULL values

If the input string is NULL, the SUBSTRING() 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(text, 8, 5) AS result
FROM example;

The following is the output:

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

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

Related Functions

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

• MariaDB LEFT() function is used to extract a substring from the beginning of a string.
• MariaDB RIGHT() function is used to extract a substring from the end of a string.
• MariaDB SUBSTR() function is an alias for the SUBSTRING() function, providing the same functionality.
• MariaDB SUBSTRING_INDEX() function is used to extract a substring from a string based on a specified delimiter.

Conclusion

The SUBSTRING() function in MariaDB is a versatile tool for extracting substrings from a given string. It allows you to specify the starting position and length of the substring, making it useful for various string manipulation tasks. By understanding the syntax and usage of this function, you can efficiently extract and manipulate data within strings in your SQL queries and applications.