The MySQL character function

MySQL provides a number of functions for our development has a lot of help, below the MySQL provides some of the common functions of the collation, first, the character processing function:

1.CONCAT ()

Usage: string link function, link string field to a piece

Example:

Select Concat (' MYSQL ', ' functioin ', ' Test ', ' Asian ');

Results:

+-----------------------------------------------+| Concat (' MYSQL ', ' functioin ', ' Test ', ' Asian ') |+-----------------------------------------------+|  Mysqlfunctiointestlasian                  |+-----------------------------------------------+

2.concat_ws ()

Usage Example: Use the specified delimiter to connect the word linked

Example:

Select CONCAT_WS (' * * * ', ' Mysql ', ' Function ', ' test ');

Results:

+---------------------------------------------+| CONCAT_WS (' * * * ', ' Mysql ', ' Function ', ' Test ') |+---------------------------------------------+| Mysql****function****test                   |

　　

3.FORMAT ()

Examples of usage: numeric formatting

Example:

Select FORMAT (3434.343434,3);

Results:

+-----------------------+| FORMAT (3434.343434,3) |+-----------------------+| 3,434.343             |+-----------------------+

4.LOWER ()

To use: Convert to lowercase characters

Example:

Select LOWER (' MYSQL ');

Results:

+----------------+| LOWER (' MYSQL ') |+----------------+| MySQL          |+----------------+

5.UPPER ()

Usage: Contrary to lower () usage

6.LEFT ()

Usage: Get left character

Example:

Mysql> Select Left (' MYSQL ', 1); +-----------------+| Left (' MYSQL ', 1) |+-----------------+| M               |+-----------------+1 row in Set (0.01 sec) mysql> Select Left (' MySQL ', 5); +-----------------+| Left (' MYSQL ', 5) |+-----------------+| MySQL           |+-----------------+1 row in Set (0.00 sec) mysql> Select Left (' MySQL ', 7); +-----------------+| Left (' MYSQL ', 7) |+-----------------+| MYSQL           |+-----------------+1 row in Set (0.00 sec)

The string is returned when the character length limit is manipulated, but must have a numeric parameter or an error will be

6.RIGHT ()

Usage: Usage and left () are similar

Mysql> Select Right (' MYSQL ', 7); +------------------+| Right (' MYSQL ', 7) |+------------------+| MYSQL            |+------------------+1 row in Set (0.00 sec) mysql> Select right (' MySQL ', 5); +------------------+| Right (' MYSQL ', 5) |+------------------+| MYSQL            |+------------------+1 row in Set (0.00 sec) mysql> Select right (' MySQL ', 2); +------------------+| Right (' MYSQL ', 2) |+------------------+| QL               |+------------------+1 row in Set (0.00 sec)

7.LENGTH ()

Usage: Gets the length of the character (including the length of the space inside the character)

Mysql> Select LENGTH (' MYSQL '); +-----------------+| LENGTH (' MYSQL ') |+-----------------+|               5 |+-----------------+1 row in Set (0.00 sec) mysql> Select LENGTH (' MY  SQL '); +-------------------+| LENGTH (' MY  SQL ') |+-------------------+|                 7 |+-------------------+1 row in Set (0.00 sec) mysql> Select LENGTH (' MY  SQL '); +---------------------+| LENGTH (' MY  SQL ') |+---------------------+|                   9 |+---------------------+1 row in Set (0.00 sec)

8.TRIM ()

Usage: Remove leading and trailing characters

Mysql> Select TRIM (' MYSQL ')    , +-----------------+| TRIM (' MYSQL ') |+-----------------+| MYSQL           |+-----------------+1 row in Set (0.00 sec) mysql> Select LENGTH (TRIM (' MySQL ')); +------------------------ -+| LENGTH (TRIM (' MYSQL ')) |+-------------------------+|                       5 |+-------------------------+1 row in Set (0.00 sec) mysql> Select LENGTH (' MySQL '); +-------------------+| LENGTH (' MYSQL ') |+-------------------+|                 7 |+-------------------+1 row in Set (0.00 sec)

9.LTRIM (), RTRIM ()

The two functions are to remove leading and trailing spaces, respectively.

10.SUBSTRING ()

Usage: intercepts the substring of the character, the first argument is the string of the action, the second is the position where the substring starts, the third is the number of substrings returned, the default is to return all, if

Returns all remaining substrings over the length

Example:

Mysql> Select SUBSTRING (' MYSQL ', 2); +----------------------+| SUBSTRING (' MYSQL ', 2) |+----------------------+| Ysql                 |+----------------------+1 row in Set (0.00 sec) mysql> Select SUBSTRING (' MySQL ', 2,2); +------------------- -----+| SUBSTRING (' MYSQL ', 2,2) |+------------------------+| YS                     |+------------------------+1 row in Set (0.00 sec) mysql> Select SUBSTRING (' MySQL ', 2,4); +------------------- -----+| SUBSTRING (' MYSQL ', 2,4) |+------------------------+| Ysql                   |+------------------------+1 row in Set (0.00 sec)

11.REPLACE ()

Usage: Replace the string function, the first argument is the operand, the second is the string to be replaced, and the third one is the replacement string

Example:

Mysql> Select REPLACE (' MySQL ', ' my ', ' REPLACE '); +---------------------------------+| Replace (' MySQL ', ' my ', ' replace ') |+---------------------------------+| Replacesql                      |+---------------------------------+1 row in Set (0.00 sec)

　　

The MySQL character function