How the SUBSTR() function works in Mariadb?

The SUBSTR() function in MariaDB is used to extract a substring from a given string. It allows you to specify the starting position and the length of the substring you want to extract. This function is commonly used for string manipulation and data extraction tasks.

Syntax

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

SUBSTR(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 SUBSTR('Hello, World!', 8, 5) AS result;

The following is the output:

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

In this example, the SUBSTR() 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 SUBSTR('MariaDB', 1, 6) AS result;

The following is the output:

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

In this example, the SUBSTR() 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 SUBSTR() 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, SUBSTR(name, -4, 3) AS result
FROM example;

The following is the output:

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

In this example, the SUBSTR() 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 SUBSTR() function extracts the substring from the specified position until the end of the string.

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

The following is the output:

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

In this example, the SUBSTR() 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 SUBSTR() function returns NULL.

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

SELECT text, SUBSTR(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 SUBSTR() 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 REPLACE() function is used to replace occurrences of a substring within a string with a new substring.
• MariaDB CONCAT() function is used to concatenate two or more strings.

Conclusion

The SUBSTR() 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.