Summary of MySQL string interception related functions

In this paper, we introduce the related functions of MySQL string interception, which are as follows:

In the work, it may be necessary to access some fields into a string as field values according to a certain divider. For example, a certain task corresponds to three results, which are stored in different data tables. At this time, the primary keys of these three different tables can be combined according to the agreed order (primary key a: primary key b: primary key c). When you need to look up the detailed information of the corresponding categories of tasks, you can intercept the string (primary key b) join table b at a specific position for operation. Just recently, I also encountered this operation, and specially combed the related functions of MySQL string interception for future review.

1. left(str, len)

Returns the number of len characters from the left of the string str. If any 1 parameter is NULL, NULL is returned.

mysql> select left('shinejaie', 5);
+---------------------------------------------------------+
| left('shinejaie', 5)                  |
+---------------------------------------------------------+
| shine                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

2. right(str, len)

Returns the character of the last len bit to the right of str. If some parameters are NULL values, NULL is returned.

mysql> select right('shinejaie', 4);
+---------------------------------------------------------+
| right('shinejaie', 4)                  |
+---------------------------------------------------------+
| jaie                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

3. substring_index(str, delim, count)

Returns the substring before the delimiter delim of the count occurrence in str. If count is a positive number, return everything to the left of the last 1 separator (because it is from the left number separator) as a substring; If count is negative, all the contents to the right of the last 1 separator (because the separator is counted from the right) are returned as substrings. Functions are case sensitive when looking for delimiters. If the value specified by the delim parameter is not found in the string str, the entire string is returned.

mysql> select substring_index('home.cnblogs.com', '.', 2);
+---------------------------------------------------------+
| substring_index('home.cnblogs.com', '.', 2)       |
+---------------------------------------------------------+
| home.cnblogs                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('home.cnblogs.com', '/', 2);
+---------------------------------------------------------+
| substring_index('home.cnblogs.com', '/', 2)       |
+---------------------------------------------------------+
| home.cnblogs.com                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

4. substring () and substr ()-- > substring (str, pos), substring (str, from, pos), substring (str, pos, len), substring (str, from, pos, for, len)

Among the above four function variants, the function form without len parameter returns the substring after position pos in str; A function form with an len parameter returns a substring of length len after position pos in str. The function form using FROM is the standard SQL syntax. The pos parameter may also take a negative value, in which case the string is taken from the end of the string str forward (rather than from front to back), starting at pos in this reverse order. In addition, negative pos parameters can be used in any form of substring () function.

mysql> select substring('shinejaie', 6);
+---------------------------------------------------------+
| substring('shinejaie',6)                |
+---------------------------------------------------------+
| jaie                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select substr('shinejaie' from 6);
+---------------------------------------------------------+
| substr('shinejaie' from 6)               |
+---------------------------------------------------------+
| jaie                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select substring('shinejaie', -9, 5);
+---------------------------------------------------------+
| substring('shinejaie', -9, 5)              |
+---------------------------------------------------------+
| shine                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

5. trim([{both | leading | trailing} [remstr] form] str)

Removes the string str from the prefix or suffix specified by remstr and returns the resulting string. If the identifier both, leading, or trailing is not specified, both is adopted by default, that is, all prefixes are deleted. remstr is actually an optional parameter, and if it is not specified, spaces are deleted.

mysql> select trim(' shinejaie  ');
+---------------------------------------------------------+
| trim(' shinejaie  ')                 |
+---------------------------------------------------------+
| shinejaie                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(leading 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(leading 'cn_' from 'cn_shinejaiecn_')       |
+---------------------------------------------------------+
| shinejaiecn_                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(both 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(both 'cn_' from 'cn_shinejaiecn_')         |
+---------------------------------------------------------+
| shinejaie                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(trailing 'cn_' from 'cn_shinejaiecn_');
+---------------------------------------------------------+
| trim(trailing 'cn_' from 'cn_shinejaiecn_')       |
+---------------------------------------------------------+
| cn_shinejaie                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Summarize