MySQL string function, mysql string

MySQL string function, mysql string
String case-sensitive Conversion

MySQL string case-sensitive conversion functions include lower (), uppper (), lcase (), and ucase ()

mysql> select lower('DDD');+--------------+| lower('DDD') |+--------------+| ddd          |+--------------+mysql> select upper('ddd');+--------------+| upper('ddd') |+--------------+| DDD          |+--------------+mysql> select lcase('DDD');+--------------+| lcase('DDD') |+--------------+| ddd          |+--------------+mysql> select ucase('ddd');+--------------+| ucase('ddd') |+--------------+| DDD          |+--------------+

In general, I select lower () and upper () to convert string case, because it is compatible with functions in other databases.

Removes spaces at the beginning and end of a string.

There are three spaces at the beginning and end of the clearing string in MySQL: ltrim (), rtrim (), trim ()

mysql> select concat('.', ltrim(' ddd '), '.');+----------------------------------+| concat('.', ltrim(' ddd '), '.') |+----------------------------------+| .ddd .                           |+----------------------------------+mysql> select concat('.', rtrim(' ddd '), '.');+----------------------------------+| concat('.', rtrim(' ddd '), '.') |+----------------------------------+| . ddd.                           |+----------------------------------+mysql> select concat('.', trim(' ddd '), '.');+---------------------------------+| concat('.', trim(' ddd '), '.') |+---------------------------------+| .ddd.                           |+---------------------------------+

The trim string function in MySQL is really powerful. It not only removes spaces at the beginning and end of a string, but also removes any specified characters. Ltrim (), rtrim () is only a subset of its functions. Let's take a look at the complete syntax of the trim function:

1. trim([{both | leading | trailing} [remstr] from] str)2. trim([remstr from] str)

1. Clear the first character of the string.

mysql> select trim(leading '.' from '..ddd..');+----------------------------------+| trim(leading '.' from '..ddd..') |+----------------------------------+| ddd..                            |+----------------------------------+

2. Clear the tail character of the string.

mysql> select trim(trailing '.' from '..ddd..');+-----------------------------------+| trim(trailing '.' from '..ddd..') |+-----------------------------------+| ..ddd                             |+-----------------------------------+

3. Clear the first and end characters of the string.

mysql> select trim(both '.' from '..ddd..');+-------------------------------+| trim(both '.' from '..ddd..') |+-------------------------------+| ddd                           |+-------------------------------+mysql> select trim('.' from '..ddd..');+--------------------------+| trim('.' from '..ddd..') |+--------------------------+| ddd                      |+--------------------------+

Trim () removes spaces at the beginning and end of the string by default.

String Truncation

MySQL string truncation functions: left (), right (), substring (), substring_index (). There are also mid () and substr (). Here, mid () and substr () are equivalent to substring () functions. substring () functions are very powerful and flexible.

1. String truncation: left (str, length)

mysql> select left('sqlstudy.com', 3);+-------------------------+| left('sqlstudy.com', 3) |+-------------------------+| sql                     |+-------------------------+

2. String truncation: right (str, length)

mysql> select right('sqlstudy.com', 3);+--------------------------+| right('sqlstudy.com', 3) |+--------------------------+| com                      |+--------------------------+

3. String truncation: substring (str, pos); substring (str, pos, len)

3.1 starts from the string's 4th character position and ends.

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

3.2 starts from the string's 4th character position and takes only 2 characters.

mysql> select substring('sqlstudy.com', 4, 2);+---------------------------------+| substring('sqlstudy.com', 4, 2) |+---------------------------------+| st                              |+---------------------------------+

3.3 starts from the position (reciprocal) of the string's 4th characters until the end.

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

3.4 starts from the string's 4th character position (reciprocal) and takes only 2 characters.

mysql> select substring('sqlstudy.com', -4, 2);+----------------------------------+| substring('sqlstudy.com', -4, 2) |+----------------------------------+| .c                               |+----------------------------------+

We noticed that in the substring (str, pos, len) function, pos can be a negative value, but len cannot be a negative value.

4. String truncation: 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 '.' (reciprocal.

mysql> select substring_index('www.sqlstudy.com.cn', '.', -2);+-------------------------------------------------+| substring_index('www.sqlstudy.com.cn', '.', -2) |+-------------------------------------------------+| com.cn                                          |+-------------------------------------------------+

4.3 If the delim parameter value cannot be 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                               |+---------------------------------------------------+