MySQL string truncation Functions

MySQL string truncation Functions

In work, some fields may need to form a string by a separator as the field value to access the database table. For example, a task pair should store three results in different data tables, in this case, the primary keys of the three different tables can be combined in the agreed order (primary key a: Primary Key B: Primary Key c ). When you need to query the details of the corresponding category of the task separately, you can intercept the string (primary key B) in a specific position and join table B to perform the operation. This operation happened recently. We specially combed the functions related to MySQL string Truncation for future review.

1. left (str, len)

Returns a string.strFrom leftlenCharacters. If any of the input values 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)

ReturnstrRight endlenCharacters. 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)

Iii. substring_index (str, delim, count)

ReturnstrCentercountOccurrence SeparatordelimThe substring. IfcountIf it is a positive number, all content on the left side of the last separator (because it is a left-side delimiter) is returned as a substring. IfcountIf the value is negative, all content of the last Separator on the right (because it is a right number separator) is returned as a substring. When searching for separators, the function is case sensitive. If the value specified by the delim parameter cannot be found in the str string, 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)

In the above four function variants, nolenThe function form of the parameter will returnstrMedium locationposSub-string after; haslenThe function form of the parameter will returnstrMedium locationposLength islen. UseFROMThe function form is the standard SQL syntax.posThe parameter may also take a negative value. In this case, the string is retrieved from the stringstrForward (rather than backward) from the endposStart to take the string. In additionposParameters can be used in any formsubstring()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 stringstrRemoveremstrThe specified prefix or suffix to return the result string. If no identifier is specifiedboth,leading, OrtrailingIs used by default.bothTo delete the prefix and suffix.remstrIt is 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)

This article permanently updates the link address: