MySQl intercept function left (), right (), substring (), Substring_index () usage

1. String intercept: Left (str, length)

Mysql> Select Left (' sqlstudy.com ', 3); +-------------------------+| Left (' sqlstudy.com ', 3) |+-------------------------+| SQL                     |+-------------------------+

2. String intercept: Right (str, length)

Mysql> Select Right (' sqlstudy.com ', 3); +--------------------------+| Right (' sqlstudy.com ', 3) |+--------------------------+| COM                      |+--------------------------+

3. String interception: Substring (str, POS); SUBSTRING (str, POS, len)

3.1 starts at the 4th character position of the string until the end.

mysql> Select substring (' sqlstudy.com ', 4); +------------------------------+| SUBSTRING (' sqlstudy.com ', 4) |+------------------------------+| study.com                    |+------------------------------+

3.2 starts at the 4th character position of the string and takes only 2 characters.

mysql> Select substring (' sqlstudy.com ', 4, 2); +---------------------------------+| SUBSTRING (' sqlstudy.com ', 4, 2) |+---------------------------------+| St                              |+---------------------------------+

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

mysql> Select substring (' sqlstudy.com ',-4); +-------------------------------+| SUBSTRING (' sqlstudy.com ',-4) |+-------------------------------+|. com                          |+-------------------------------+

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

mysql> Select substring (' sqlstudy.com ', -4, 2); +----------------------------------+| SUBSTRING (' sqlstudy.com ', -4, 2) |+----------------------------------+|. C                               |+--------------------------------- -+

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 Intercept all characters before the second '. '.

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. ', 2); +------------------------------------------------+ | Substring_index (' www.sqlstudy.com.cn ', '. ', 2) |+------------------------------------------------+| Www.sqlstudy                                   |+------------------------------------------------+

4.2 Intercept all characters after the second '. ' (Countdown).

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. ',-2); +------------------------------------------------ -+| Substring_index (' www.sqlstudy.com.cn ', '. ',-2) |+-------------------------------------------------+| com.cn                                          |+-------------------------------------------------+

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

Mysql> Select Substring_index (' www.sqlstudy.com.cn ', '. CoC ', 1); +---------------------------------------------- -----+| Substring_index (' www.sqlstudy.com.cn ', '. CoC ', 1) |+---------------------------------------------------+| www.sqlstudy.com.cn                               |+---------------------------------------------------+

MySQl intercept function left (), right (), substring (), Substring_index () usage