How the MID() function works in Mariadb?

The MID() function is a useful tool for extracting a substring from a given string.

Posted on

The MID() function is a useful tool for extracting a substring from a given string. It can be used for various purposes, such as parsing, formatting, and manipulating strings.

Syntax

The syntax of the MID() function is as follows:

MID(str, pos, len)

The function takes three arguments:

  • str: A string value that represents the input string to extract the substring from. It can be any valid expression that returns a string, such as a column name, a literal, or a function.
  • pos: An integer value that represents the starting position of the substring to be extracted. It can be any valid expression that returns an integer, such as a column name, a literal, or a function. The position is counted from 1, meaning that the first character of the string is at position 1.
  • len: An integer value that represents the length of the substring to be extracted. It can be any valid expression that returns an integer, such as a column name, a literal, or a function. The length is counted in characters, not bytes.

The function returns a string value that represents the substring of the input string, starting from the specified position and having the specified length. If any of the arguments are NULL or invalid, the function returns NULL.

Examples

In this section, we will show some examples of how to use the MID() function in different scenarios.

Example 1: Extracting a substring from a literal string

Suppose you want to extract a substring from a literal string, such as ‘Hello World’. You can use the MID() function to do so. For example, you can execute the following statement:

SELECT MID('Hello World', 2, 3);

This will return the substring of the string ‘Hello World’, starting from the second position and having a length of three characters, which is ’ell’. For example, the result might look like this:

+--------------------------+
| MID('Hello World', 2, 3) |
+--------------------------+
| ell                      |
+--------------------------+

Note that the position and length are counted in characters, not bytes. For example, the substring of the string ‘Hello World’, starting from the first position and having a length of five bytes, is ‘Hello’, not ‘Hell’.

Example 2: Extracting a substring from a column value

Suppose you have a table called products that stores the information of various products, such as their product_id, name, and price. The name column is a string value that represents the name of the product. You want to extract the first three characters of the name of each product, so that you can create a short name, a prefix, or a code for the product. You can use the MID() function to do so. For example, you can execute the following statement:

SELECT product_id, name, MID(name, 1, 3) AS short_name FROM products;

This will return the product_id, name, and the first three characters of the name of each product, or an empty result set if the table is empty. For example, the result might look like this:

+------------+--------------------+------------+
| product_id | name               | short_name |
+------------+--------------------+------------+
| 1          | Apple iPhone 12    | App        |
| 2          | Samsung Galaxy S21 | Sam        |
| 3          | Huawei P40 Pro     | Hua        |
| 4          | Xiaomi Mi 11       | Xia        |
| 5          | OnePlus 9          | One        |
+------------+--------------------+------------+

Note that the short name is the substring of the name, starting from the first position and having a length of three characters. For example, the short name of the product ‘Apple iPhone 12’ is ‘App’, not ‘Appl’.

Example 3: Extracting a substring from a function result

Suppose you want to extract a substring from the result of a function, such as NOW() or UUID(). You can use the MID() function to do so. For example, you can execute the following statement:

SELECT MID(NOW(), 1, 10);

This will return the substring of the current date and time, starting from the first position and having a length of 10 characters, which is the date in the format ‘YYYY-MM-DD’. For example, the result might look like this:

+-------------------+
| MID(NOW(), 1, 10) |
+-------------------+
| 2024-01-01        |
+-------------------+

Similarly, you can execute the following statement:

SELECT MID(UUID(), 1, 8);

This will return the substring of the universally unique identifier (UUID), starting from the first position and having a length of eight characters, which is the first part of the UUID. For example, the result might look like this:

+-------------------+
| MID(UUID(), 1, 8) |
+-------------------+
| bdfcd6ae          |
+-------------------+

There are some other functions that are related to the MID() function and can be used to perform other string operations in Mariadb. Here are some of them:

  • LEFT(): This function returns the leftmost part of a string, with a specified length.
  • RIGHT(): This function returns the rightmost part of a string, with a specified length.
  • SUBSTRING(): This function returns a substring of a string, with a specified position and length. It is equivalent to the MID() function, but with a different syntax.
  • SUBSTRING_INDEX(): This function returns a substring of a string, before or after a specified delimiter.
  • LENGTH(): This function returns the length of a string, in bytes.
  • CHAR_LENGTH(): This function returns the length of a string, in characters.

Conclusion

The MID() function is a powerful and flexible function that can help you extract a substring from a given string. It can be used for various purposes, such as parsing, formatting, and manipulating strings. You can also use some other related functions to extract other parts of a string, such as left, right, substring, or substring index. By using these functions, you can achieve a better analysis and understanding of your string data.