MySQL string Intercept

Reproduced:

MySQL string intercept function:left(), right (), substring (), Substring_index (). There is also mid (), substr (). wheremid (), substr ( ) is equivalent to the substring () function,the function ofsubstring ( ) is very powerful and flexible.

1. string intercept:left(str, length)

Select Left (' 2017-11-14t16:00:00.000z ', 3) from DUAL
Results:201

2. string intercept:right(str, length)

Select Right (' 2017-11-14t16:00:00.000z ', 3) from DUAL
results:00Z

Instance:

# Two characters after querying a field
Select Right (LAST3, 2) as Last2 from Historydata limit 10;
# Update the two-bit characters from the expected field to another field
Update ' historydata ' Set ' Last2 ' =right (LAST3, 2);

3. string interception:substring (str, POS); substring (str, POS, len)

3.1 Starts at the 4-character position of the string until it ends.

Select substring (' 2017-11-14t16:00:00.000z ', 4) from DUAL
results:7-11-14t16:00:00.000z

3.2 Starts at the 1-character position of the string , taking only ten characters.

Select substring (' 2017-11-14t16:00:00.000z ', 1,10) from DUAL
results:2017-11-14

3.3 Starts from the 4 character position (reciprocal) of the string until it ends.

Select substring (' 2017-11-14t16:00:00.000z ',-4) from DUAL
results:000Z

3.4 is taken from the 4- character position (reciprocal) of the string and takes only 2 characters.

Select substring (' 2017-11-14t16:00:00.000z ', -4,2) from DUAL
Results:00

we notice that in function substring (str,pos, len) , pos can be negative, but len cannot take a negative value.

4. string interception:substring_index (str,delim,count)

4.1 interception of the second '. ' all previous characters.

Select Substring_index (' www.example.com ', '. ', 2);
Results: www.example

4.2 interception of the second '. ' all characters after the (countdown).

Select Substring_index (' www.example.com ', '. ',-2);
results:example.com

4.3 If the value specified by the Delim parameter is not found in the string , the entire string is returned

Select Substring_index (' www.example.com ', '. CoC ', 1);
Results: www.example.com

MySQL string Intercept