MySQL Basics-character functions

1.UPPER and UCase
returns the string str, which changes all characters to uppercase based on the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.

2.LOWER and LCase
returns the string str, which changes all characters to lowercase according to the current character set mapping (the default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.

3.find_in_set (str,strlist)
if the string str is in table strlist consisting of n substrings, returns a value of 1 to N. A string table is a string consisting of "," separated substrings. If the first argument is a constant string and the second argument is a column of type set, the Find_in_set () function is optimized to use bit arithmetic! If STR is not inside strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first parameter contains a ",", the function will not work correctly.

4.FIELD (str,str1,str2,str3,...)
return str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation.
SELECT FIELD (' A ', ' AB ', ' a ', ' C ')
--Return 2

5.ELT (n,str1,str2,str3,...)
if n= 1, return str1, if n= 2, return str2, and so on. If n is less than 1 or greater than the number of arguments, NULL is returned. ELT () is a field () inverse operation.

6.REPLACE (STR,FROM_STR,TO_STR)
returns the string str, whose string from_str all occurrences are replaced by the string to_str.
SELECT REPLACE (' ABC ', ' B ', ' 123 ')
--Back to a123c

7.REPEAT (Str,count)
returns a string consisting of the string str that repeats counttimes times.
if Count <= 0, an empty string is returned. If STR or count is NULL, returns NULL.
SELECT REPEAT (' A ', 5);
--Back to AAAAA

8.REVERSE (str)
returns a string that reverses the character order of Str.
SELECT REVERSE (' ABC ');
--Back to CBA

9.INSERT (STR,POS,LEN,NEWSTR)
returns the string str at position POS starting with a substring and Len characters long substrings are replaced by string newstr.
SELECT INSERT (' ABC123 ', 4,2, ' DE ')
--Back to ABCDE3


10.SUBSTRING (str from POS)
returns a substring from the starting position of the string str from POS.
The subscript for the ps:mysql database starts at 1.
SELECT SUBSTRING (' ABC123 ' from 4);
--Back to 123

11.substring_index (Str,delim,count)
Returns the substring that appears after the delimiter Delim from the count of the string str. If Count is a positive number, returns all characters from the last delimiter to the left (from the left). If count is negative, returns the last delimiter to the right of all characters (from the right number).
SELECT substring_index (' ab1ab2ab3ab4 ', ' AB ', 3);
--Back to AB1AB2


12.LTRIM (str)
returns the string str whose preceding space character was deleted.
SELECT CONCAT (' 1 ', LTRIM (' ABC '), ' 2 ')
return 1ABC 2

13.RTRIM (str)
returns the string str whose trailing space character was deleted.
SELECT CONCAT (' 1 ', RTRIM (' ABC '), ' 2 ')
returns back to 1ABC 2

14.TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str)
returns the string str with all of its remstr prefixes or suffixes removed.
if no modifier both, leading, or trailing is given, both is assumed.
If REMSTR is not specified, the space is deleted.
SELECT CONCAT (' 1 ', TRIM (' ABC '), ' 2 ');
returns the return 1ABC2

15.MID (Str,pos,len)
returns a substring of Len characters from the string str, starting at position pos.
SELECT MID (' ABC123 ', 4,2);
returns a return of

16.LPAD (STR,LEN,PADSTR)
returns the string str, left with the string padstr filled until STR is Len characters long.
SELECT lpad (' 1 ', 5, '-');
back----1

17.RPAD (STR,LEN,PADSTR)
returns the string str, to the right with a string padstr until Str is len characters long.
SELECT rpad (' 1 ', 5, '-');
returns 1----

Left (Str,len)
returns the leftmost Len character of a string str.

19.RIGHT (Str,len)
returns the rightmost Len character of a string str.

20. Position control Function
POSITION (substr in str)
returns the substring substr in the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.

LOCATE (Substr,str,pos)
returns the substring substr where the first occurrence of the string Str begins, starting at position pos. If the substr is not inside STR, return 0.

INSTR (STR,SUBSTR)
returns the position of the first occurrence of a substring substr in the string str. This is the same as the locate () with 2 parameters, except that the parameters are reversed.

SELECT POSITION (' AB ' in ' 123ABC ');
--Return 4

SELECT LOCATE (' AB ', ' ab1ab2ab3 ', 5);
--Return 7

SELECT INSTR (' 123ab1ab2ab3 ', ' AB ');
--Return 4

21. function to get string length
Length (str), which calculates the field lengths (in bytes).
octet_length (str), calculates the number of bytes in a string, and under UTF8, a Chinese occupies 3 lengths; GBK, a Chinese occupies 2 lengths.
char_length (str), the return value is the length of the string str, and the length of the unit is a character. A multibyte character counts as a single character.
character_length (str), Character_length () is a synonym for Char_length ().
bit_length (str) returns 2 binary length.

SELECT LENGTH (' Hello 123abc ');--12
SELECT octet_length (' Hello 123abc ');--12
SELECT char_length (' Hello 123abc ');--8
SELECT character_length (' Hello 123abc ');--8

22. Merging multiple strings, or multiple fields in a table
CONCAT (str1,str2,...)
returns the string from the parameter link. Returns NULL if any parameter is null. There can be more than 2 parameters.
A numeric parameter is transformed into an equivalent string form.
SELECT CONCAT (123, ' ABC ');



23. Binary Conversion
BIN (N): Returns a string representation of the binary value N, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,2). If n is null, returns NULL.
OCT (N): Returns the representation of a string of octal value N, where n is a long integer number, which is equivalent to Conv (n,10,8). If n is null, returns NULL.
Hex (N): Returns the hexadecimal value n a string representation, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,16). If n is null, returns NULL.
ASCII (STR): Returns the ASCII code value of the leftmost character of the string str. If Str is an empty string, return 0. If STR is NULL, returns NULL.

MySQL Basics-character functions