MySQL Self-study chapter (eight)

2, String function (1) function to calculate the number of characters in a string and the length of a string

Char_length (str) returns The number of characters that the string str contains. A multibyte character is counted as a single character

Select Char_length (' Date '), char_length (' egg '), char_length (' China ');

（2) to combine string functionsCONCAT (s1,s2 ...),Concat_ws (x,s1,s2 ...)

CONCAT (S1,s2 ...) function, if either argument is NULL , the return NULL ;

Concat_ws (X,s1,s2 ...) function, x a connector that represents the connection string;

Select Concat (' My ', ' SQL '), concat (' my ', null, ' SQL ');

Select Concat_ws ('-', ' My ', ' SQL ');

（3) function to replace stringINSERT (S1,X,LEN,S2)

INSERT (S1,X,LEN,S2) returns the string s1whose substring starts at the x position and is replaced by the string S2 Len character.

Select Insert (' Quest ', 2,4, ' what '), insert (' Quest ', -1,4, ' What '), insert (' Quest ', 3,10, ' what ');

（4) Letter-Case conversion function

LOWER (str) or LCASE (str) can convert letters in a string to lowercase letters

Select lower (' qazwsx '), LCase (' qazwsx ');

UPPER (str) or UCASE (x) can convert all letters in a string to uppercase letters

Select Upper (' qazwsx '), UCase (' qazwsx ');

（5a function that gets a string of the specified lengthLeft (s,n)and theRight (s,n)

Left (s,n) returns the leftmost n characters from the beginning of the string s

Select Left (' Football ', 5);

Right (S,n) returns the rightmost n character of the string str

Select right (' Football ', 4);

(6) A function that fills a stringLpad (S1,LEN,S2)and theRpad (S1,LEN,S2)

Lpad (S1,LEN,S2) returns a string s1whose left side is supplemented by a string S2 to the len character length. If The length of the S1 is greater than Len, the return value is shortened to the len character

Select Lpad (' Hello ', 4, '?? '), Lpad (' Hello ', 10, '? ');

Rpad (S1,LEN,S2) returns the string s1, whose right is filled by the string S2 to the len character length. If the string S1 length is greater than Len, the return value is shortened to the len character length.

Select Rpad (' Hell0 ', 4, '? '), Rpad (' Hello ', 10, '? ');

（7) function to remove spacesLTRIM (s),RTRIM (s),TRIM (s)

LTRIM (s) delete The left space of the string s

Select ' (book) ', Concat (' ('), LTrim (' book '), ') ');

RTRIM (s) remove The space to the right of the string s

Select ' (book) ', Concat (' ('), RTrim (' book '), ') ');

TRIM (s) remove space on both sides of string s

Select ' (book) ', Concat (' ('), Trim (' book '), ') ');

MySQL Self-study chapter (eight)