![Analysis of common basic operations of MySQL database [create, view, modify and delete database]](/upload/images/67cad65d7d314.webp)
Summary of MySQL string interception related functions
|
This article introduces MySQL string interception related functions. The specific contents are as follows: At work, you may need to combine certain fields into a string using a certain separator as the field value and store it in a database table. For example, a task corresponds to three results, which are stored in different data tables. In this case, the primary keys of these three different tables can be combined in the agreed order (primary key a: primary key b: primary key c). When you need to check the detailed information of the corresponding categories of tasks separately, you can intercept the string (primary key b) at a specific position and join table b for operation. I just happened to encounter this operation recently, so I specially sorted out the related functions of MySQL string interception for future review. 1. left(str, len) Returns len characters from the left of string str. If either argument is NULL, returns NULL.
mysql> select left('shinejaie', 5);
+---------------------------------------------------------+
| left('shinejaie', 5) |
+---------------------------------------------------------+
| shine |
+---------------------------------------------------------+
1 row in set (0.00 sec)2. right(str, len) Returns the last len characters from the right of str. If any of the arguments is NULL, 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 of str before the count occurrence of the delimiter delim. If count is positive, everything to the left of the last delimiter (because it is counted from the left) is returned as a substring; if count is negative, everything to the right of the last delimiter (because it is counted from the right) is returned as a substring. The function is case sensitive when searching 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) In the above four function variants, the function form without the len parameter will return the substring after the position pos in str; the function form with the len parameter will return the substring after the position pos in str with a length of len. The function form of FROM uses standard SQL syntax. The pos parameter may also take a negative value, in which case the string is retrieved from the end of the string str forward (rather than from the front to the back), starting from position pos in reverse order. Additionally, a negative pos parameter can be used with any form of the 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) Remove the prefix or suffix specified by remstr from the string str and return the resulting string. If the identifiers both, leading, or trailing are not specified, the default is both, which means that both the prefix and suffix are removed. 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)Summarize The above is a summary of MySQL string interception related functions introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website! You may also be interested in:
|
<<: Ubuntu 16.04 image complete installation tutorial under VMware
>>: Detailed explanation of TypeScript 2.0 marked union types
Recommend
Detailed description of the use of advanced configuration of Firewalld in Linux
IP masquerading and port forwarding Firewalld sup...
Windows uses VMware to create a Linux virtual machine and install the CentOS7.2 operating system
Table of contents 1. Follow the wizard to create ...
How to monitor Tomcat using LambdaProbe
Introduction: Lambda Probe (formerly known as Tom...
XHTML introductory tutorial: Use of list tags
Lists are used to list a series of similar or rela...
Example code for implementing 3D text hover effect using CSS3
This article introduces the sample code of CSS3 t...
CSS3 custom scroll bar style::webkit-scrollbar sample code detailed explanation
The default scroll bar style in Windows is ugly, ...
Super detailed MySQL8.0.22 installation and configuration tutorial
Hello everyone, today we are going to learn about...
Ubuntu boot auto-start service settings
How to create a service and auto-start it in Ubun...
Detailed tutorial on customizing the installation path of MySQL 5.7.18 version (binary package installation)
Installation path: /application/mysql-5.7.18 1. P...
HTML Learning Notes--Detailed Explanation of HTML Syntax (Must Read)
1. What is HTML markup language? HTML is a markup...
How to reset MySQL root password under Windows
Today I found that WordPress could not connect to...
Detailed process record of Vue2 initiating requests using Axios
Table of contents Preface Axios installation and ...
10 Tips for Mobile App User Interface Design
Tip 1: Stay focused The best mobile apps focus on...
Summary of MySQL's commonly used concatenation statements
Preface: In MySQL, the CONCAT() function is used ...
The relationship between JS constructor and instantiation and prototype introduction
Table of contents 1. Constructor and instantiatio...