Usage of the string function substring_index intercepted by mysql.

Usage of the string function substring_index intercepted by mysql.

The intercepted string is 15,151,152, 16', which can be considered as an ip address (although it is not an iP address ),

Then extract the part before each comma.

In the string, use a comma as the index to obtain characters with different index bits.

The result is as follows:

SELECT SUBSTRING_INDEX ('1970, 16', ',', 1) ;=> the result is: 15 SELECT SUBSTRING_INDEX ('1970, 16 ',',', 2), ',',-1) ;=> the expected result is: 151 SELECT SUBSTRING_INDEX ('2017, 16', ',',-2 ),', ', 1) ;=> the result is: 152 SELECT SUBSTRING_INDEX ('2017, 16',', ',-1) ;=> the result is 16.

Mysql string Truncation

Use the SUBSTRING_INDEX function of MYSQL to implement your functions. The syntax of this function is as follows:

SUBSTRING_INDEX (str, delim, count)
Returns the substring after the delimiter delim that appears from the count of the str string. If count is a positive number, all characters from the last separator to the left (from the left) are returned. If count is a negative number, return all characters (from the right) from the last separator to the right ).
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ', 2 );
-> 'Www. mysql'
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ',-2 );
-> 'Mysql. com'
This function is reliable for multiple bytes.

If your field name is xxx, the value is 201207 | 123 | 23.5 | 100 | 50 | and 20120703 | 19 | 212 |, the function for obtaining 3rd content 23.5 is:

SUBSTRING_INDEX (xxx, '|', 3), '|',-1)

The functions in get all the content starting from 3rd, and the functions outside get the 1st content of the function result.

Methods for intercepting string functions in MySQL

The string function of MySQL intercepts characters, which is more powerful, concise, and convenient than program interceptions (such as PHP). So I would like to share it here and hope it will be useful to you. The following implementation methods: