First, ASCII
ASCII (str)
Returns the ASCII code value of the leftmost character of the string str. If Str is an empty string, returns 0. If STR is NULL, returns NULL.
Second, ORD
ORD (str)
If the string str leftmost character is a multibyte character, by using the format (the *256+ (the second byte ASCII code)) [*256+third byte ASCII code ...] Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value returned with the ASCII () function.
Third, CONV
CONV (N,from_base,to_base)
Transforms a number between different numeric bases. Returns a string number for the number n, from the From_base base transformation to the to_base base, or null if any argument is null. Parameter n is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the largest base is 36. If To_base is a negative number, n is considered to be a signed digit, otherwise n is treated as unsigned. Conv work with 64-bit precision.
Mysql> Select CONV ("a", 16,2);
-> ' 1010 '
mysql> Select CONV ("6E", 18,8);
-> ' 172 '
mysql> select CONV ( -17,10,-18);
-> '-H '
mysql> select CONV (10+ "ten" + ' +0xa,10,10 ');
-> ' 40 '
Four, BIN
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.
Five, OCT
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.
VI. HEX
HEX (N)
Returns the hexadecimal value n the representation of a string, where n is a long integer (BIGINT) number, which is equivalent to Conv (n,10,16). If n is null, returns NULL.
Mysql> Select HEX (255);
Seven, CHAR
CHAR (N,...)
CHAR () interprets the argument as an integer and returns a string consisting of the ASCII code characters of these integers. The null value is skipped.
Mysql> Select CHAR (77,121,83,81, ' ");
-> ' MySQL '
mysql> Select CHAR (77,77.3, ' 77.3 ');
-> ' MMM '
Eight, Concat/concat_ws
concat (str1,str2,...)
Returns a string from the argument's link. Returns null if any argument is null. can have more than 2 parameters. A numeric parameter is transformed into an equivalent string form.
Mysql> Select CONCAT (' My ', ' S ', ' QL ');
-> ' MySQL '
mysql> Select CONCAT (' My ', NULL, ' QL ');
-> NULL
mysql> Select CONCAT (14.3);
-> ' 14.3 '
Concat_ws (separator,str1,str2,...)
Concat_ws () represents CONCAT with Separator and is a special form of CONCAT (). The first parameter is the separator for the other arguments. The position of the separator is placed between the two strings to which you want to connect. The delimiter can be a string, or it can be another parameter.
SELECT Concat_ws (";", Id,title) from my_table LIMIT;
SELECT Concat_ws (";", ' AA ', ' BB ') from my_table
Nine, Length/octet_length/char_length/character_length
LENGTH (str)/octet_length (str): Number of bytes
Char_length (str)/character_length (str): Number of characters
Ten, LOCATE
The function is multiple-byte reliable.
LOCATE (SUBSTR,STR)
Returns the substring substr the first occurrence of the string str, if SUBSTR is not inside STR, returns 0.
LOCATE (Substr,str,pos)
Returns the substring substr the position of the first occurrence of the string str, starting at the location POS. If SUBSTR is not inside STR, return 0.
Xi. Lpad/rpad
Lpad (STR,LEN,PADSTR)
Returns the string str, and the left is filled with a string padstr until Str is len characters long.
Rpad (STR,LEN,PADSTR)
Returns the string str, and the right is filled with a string padstr until Str is len characters long.
12, Lelt/right
Left (Str,len)
Returns the leftmost Len character of the string str.
Right (Str,len)
Returns the rightmost Len character of the string str.
13, SUBSTRING
SUBSTRING (Str,pos,len)
Returns a substring of Len characters from String str, starting at position pos.
SUBSTRING (Str,pos)
Returns a substring from the starting position of string Str.
14, Substring_index
Substring_index (Str,delim,count)
Returns a substring of Delim after the occurrence of the delimiter from the count of string str. If Count is a positive number, returns all characters from the last separator to the left (from the left). If count is a negative number, returns the last separator to all characters on the right (from the right).
This function is reliable for multiple bytes.
Mysql> Select Substring_index (' www.mysql.com ', '. ', 2);
-> ' www.mysql '
mysql> Select Substring_index (' www.mysql.com ', '. ',-2);
-> ' mysql.com '
XV, Trim/ltrim/rtrim
TRIM ([BOTH | Leading | Trailing] [REMSTR] from] str)
Returns the string str with all of its remstr prefixes or suffixes removed. If no modifiers both, leading, or trailing are given, the both is assumed. If the REMSTR is not specified, the space is deleted.
Mysql> Select TRIM (' Bar ');
-> ' Bar '
mysql> Select TRIM (Leading ' x ' from ' xxxbarxxx ');
-> ' barxxx '
mysql> select TRIM (BOTH ' x ' from ' xxxbarxxx ');
-> ' Bar '
mysql> select TRIM (Trailing ' xyz ' from ' barxxyz ');
-> ' Barx '
LTRIM (str)
Returns the string str whose preceding space character was deleted.
RTRIM (str)
Returns the string str whose trailing space character has been deleted.
16, Space
Space (N)
Returns a string consisting of n white space characters.
17, REPLACE
REPLACE (STR,FROM_STR,TO_STR)
Returns the string str, where all occurrences of the string from_str are replaced by string to_str.
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');
-> ' WwWwWw.mysql.com '
18, REPEAT
REPEAT (Str,count)
Returns a string of string str that is repeated counttimes times. If Count <= 0, returns an empty string. Returns null if STR or count is null.
19, REVERSE
REVERSE (str)
Returns the string str of the reversed character order.
20, INSERT
INSERT (STR,POS,LEN,NEWSTR)
Returns the string str, the substring at the beginning of the position Pos and the Len character long substring is replaced by the string newstr.
Mysql> Select INSERT (' Quadratic ', 3, 4, ' What ');
-> ' quwhattic '
21. ELT
ELT (N,STR1,STR2,STR3,...)
If n= 1, return str1, if n= 2, return str2, and so on. Returns NULL if n is less than 1 or greater than the number of arguments. ELT () is a field () inverse operation.
22. FIELD
FIELD (STR,STR1,STR2,STR3,...)
Back to Str in str1, str2, STR3, ... The index of the manifest. If STR is not found, return 0. FIELD () is an ELT () inverse operation.
23, Find_in_set
Find_in_set (Str,strlist)
If string str returns a value of 1 to N in the table strlist composed of n substrings. 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 and the bitwise operation is used! If STR is not inside the strlist or if Strlist is an empty string, return 0. Returns null if any one of the arguments is null. If the first argument contains a "," the function will not work correctly.
24, Make_set
Make_set (BITS,STR1,STR2,...)
Returns a collection containing a string of substrings separated by the "," character, consisting of a string of corresponding bits in the bits collection. STR1 corresponds to bit 0,str2 corresponding bit 1, and so on. In str1, str2, ... The null string in is not added to the result.
Mysql> SELECT make_set (1, ' A ', ' B ', ' C ');
-> ' A '
mysql> SELECT make_set (1 | 4, ' hello ', ' nice ', ' world ');
-> ' Hello,world '
mysql> SELECT make_set (0, ' a ', ' B ', ' C ');
-> '
25, Export_set
Export_set (Bits,on,off,[separator,[number_of_bits])
Returns a string, where you get an "on" string for each bit in "bits", and you get a "off" string for each reset (reset) bit. Each string is delimited by "separator" (Default ",") and Only "Number_of_bits" (default 64) bits of "bits" are used.
26, Lower/lcase/upper/ucase
LCASE (str)/lower (str): Returns the string str, which changes all characters to lowercase according to the current character set mapping (default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.
UCASE (str)/upper (str): Returns the string str, which changes all characters to uppercase according to the current character set mapping (default is Iso-8859-1 Latin1). This function is reliable for multiple bytes.
27, Load_file
Load_file (file_name)
Reads the file and returns the contents of the file as a string. Files must be on the server, you must specify the full pathname to the file, and you must have file permissions. The file must all content be readable and less than max_allowed_packet. The function returns null if the file does not exist or because one of the above reasons cannot be read out.
The above is a small series for everyone to bring the MySQL string function in detail (recommended) All content, I hope that we support cloud Habitat Community ~