Shulou(Shulou.com)06/01 Report--

This article introduces MySQL string interception related functions, as follows:

In the work, some fields may need to form a character string as a field value to be accessed into the database table according to a certain delimiter. For example, three results corresponding to a certain task are stored in different data tables respectively. In this case, 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 it is necessary to look up the detailed information of the corresponding category of the task separately, you can intercept the character string (primary key b) at a specific position to join the table b for operation. Just recently encountered this operation, specially MySQL string interception related functions to do a comb, so that future review.

left(str, len)

Returns len characters of string str from the left. Returns NULL if either argument is NULL.

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

right(str, len)

Returns the last len character on the right side of str. If any of the arguments are NULL, NULL is returned.

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

substring_index(str, delim, count)

Returns the substring preceding the count occurrence of the delimiter delim in str. If count is positive, everything to the left of the last delimiter (because it counts from the left) is returned as a substring; if count is negative, everything to the right of the last delimiter (because it counts from the right) is returned as a substring. 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)

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

Of the four variants, the function form without len returns a substring of length len from position pos in str; the function form with len returns a substring of length len from position pos in str. The functional form using FROM is standard SQL syntax. The pos parameter may also take a negative value, in which case the string is fetched from the end of str onwards (rather than from front to back), starting at pos in this reverse order. In addition, negative pos arguments 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)

Returns the string str without the prefix or suffix specified by remstr. If the identifier both, leading, or trailing is not specified, both is used by default, i.e. both prefixes and suffixes are deleted. remstr is actually an optional parameter; 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)

summary

The above is a summary of MySQL string interception related functions introduced by Xiaobian. I hope it will help you. If you have any questions, please leave a message to me. Xiaobian will reply to you in time. Thank you very much for your support!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.