MySQL Learning 17: operator and function character functions

MySQL character function

The following are some of the commonly used character functions in MySQL databases:

(1) Character Connection

First create a database named Imooc, and then use the concat () character join function to query:

CREATE DATABASE Imooc;

SELECT CONCAT (' Imooc ', ' MySQL ');

SELECT CONCAT (' Imooc ', '-', ' MySQL ');

Pre-data preparation

CREATE TABLE Test (

First_Name VARCHAR (20),

Last_Name VARCHAR (10)

);

DESC test;

Inserting 4 records into the test data table

INSERT test VALUES (' A ', ' B ');

INSERT test VALUES (' C ', ' D ');

INSERT test VALUES (' tom% ', ' 123 ');

INSERT Test VALUES (NULL, ' 11 ');

We're going to combine the two fields in the test data table to query and name the output.

SELECT * from test;

SELECT CONCAT (first_name,last_name) as FullName from test;

(2) using the specified delimiter for character connections

SELECT concat_ws (' | ', ' A ', ' B ', ' C ');

SELECT concat_ws ('-', ' Imooc ', ' MySQL ', ' Functions ');

(3) digital formatting

The numeric formatting function returned by format () results in the result of preserving the specified number of decimal digits after rounding.

SELECT FORMAT (12560.74,2);

SELECT FORMAT (12560.74,1);

SELECT FORMAT (12560.74,0);

(4) Convert to lowercase and converted to uppercase letters

SELECT LOWER (' MySQL ');

SELECT UPPER (' MySQL ');

(5) Get left character and get right character

Gets the number of characters that are the left or right character of the specified number of digits.

SELECT left (' MySQL ',2);

SELECT LOWER (left (' MySQL ', 2));

SELECT right (' MySQL ',3);

(6) Get string length (including spaces)

SELECT LENGTH (' MySQL ');

SELECT LENGTH (' My SQL ');

(7) Remove leading and trailing spaces and delete leading and trailing spaces at the same time

SELECT LTRIM (' MySQL ');

SELECT LENGTH (' MySQL ');

SELECT LENGTH (LTRIM (' MySQL '));

SELECT LENGTH (RTRIM (' MySQL '));

SELECT LENGTH (TRIM (' MySQL '));

(8)TRIM () function to delete other characters at the same time

SELECT TRIM (leading '? ') From '?? MySQL??? ');

SELECT TRIM (TRAILING '? ') From '?? MySQL??? ');

SELECT TRIM (BOTH '? ') From '?? MySQL??? ');

(9) string substitution function

The TRIM () function removes only the leading and subsequent question marks of the string, which requires substitution with a string substitution function

SELECT TRIM(BOTH '? ') From '?? My?? SQL??? ');

SELECT REPLACE('?? My?? SQL??? ', '? ', ');

(10) string intercept function

The string intercept index in the MySQL database starts at 1. This function also supports negative numbers, that is, to intercept from behind.

SELECT SUBSTRING (' MySQL ', for each);

SELECT SUBSTRING (' MySQL ', 3);

SELECT SUBSTRING (' MySQL ',-1);

(11) Pattern matching

% (Percent semicolon): represents any character.

_ (Underscore): represents any one character.

SELECT ' MySQL ' like ' M% ';

SELECT * FROM Test WHERE first_name like '%o% ';

SELECT * FROM Test WHERE first_name like '%1%% ' ESCAPE ' 1 ';

MySQL Learning 17: operator and function character functions