One, Asciiascii (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.
Second, Ordord (str) If the leftmost character of the string str is a multibyte character, by using the format (first byte ASCII code) *256+ (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 multi-byte character. Returns the same value returned from the ASCII () function.
Convconv (n,from_base,to_base) transforms numbers between different digital bases. Returns a string number of N, from the From_base base transform 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 as 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 count, otherwise n is treated as an unsigned number. The conv works with 64-bit precision. Mysql> Select CONV ("a", 16,2);-> ' 1010 ' mysql> select CONV ("6E", 18,8);-> ' 172 ' mysql> select CONV ( -17,10,- ;-> '-h ' mysql> Select CONV (+ + ' + ' +0xa,10,10);-> ' 40 '
Four, Binbin (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.
V. OCTOCT (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.
Six, Hexhex (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. Mysql> Select HEX (255);
Vii. Charchar (N,...) CHAR () interprets parameters as integers and returns a string consisting of ASCII code characters for those integers. A null value was 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 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. Mysql> Select CONCAT (' My ', ' S ', ' QL ');-> ' MySQL ' mysql> select CONCAT (' My ', NULL, ' QL ');-> nullmysql> Selec T CONCAT (14.3);-> ' 14.3 '? Concat_ws (SEPARATOR,STR1,STR2,...) Concat_ws () represents CONCAT with Separator as a special form of CONCAT (). The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be another parameter. Select Concat_ws (";", Id,title) from my_table LIMIT 100; Select Concat_ws (";", ' AA ', ' BB ') from my_table
Ix. length/octet_length/char_length/character_lengthlength (str)/octet_length (str): Number of bytes char_length (str)/CHARACTER_ LENGTH (str): Number of characters
Ten, locate the function is multi-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 a substring substr the first occurrence of the string str, starting at position pos. If the substr is not inside STR, return 0.
Xi. Lpad/rpadlpad (STR,LEN,PADSTR) returns the string str, and the left is filled with a string padstr until Str is len long. Rpad (STR,LEN,PADSTR) returns the string str, and the right side is filled with a string padstr until Str is len characters long. 12, Lelt/rightleft (Str,len) returns the leftmost Len character of the string str. Right (Str,len) returns the rightmost Len character of the string str. 13, substringsubstring (Str,pos,len) returns a Len character substring from the string str, starting at position pos. SUBSTRING (str,pos) returns a substring from the starting position of the string str.
14, Substring_indexsubstring_index (str,delim,count) returns the substring that appears after the delimiter Delim from 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). 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/rtrimtrim ([BOTH | Leading | TRAILING] [REMSTR] from] str) returns the string str, with all 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. Mysql> Select Trim (' Bar ');-> ' bar ' mysql> Select trim (Leading ' x ' from ' xxxbarxxx ');-> ' barxxx ' mysql> sele CT 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 a string that has been removed after its trailing space character, Str.
16, Spacespace (n) returns a string consisting of n space characters.
17, Replacereplace (STR,FROM_STR,TO_STR) returns the string str, and all occurrences of its string from_str are replaced by the string to_str.
Mysql> Select REPLACE (' www.mysql.com ', ' w ', ' Ww ');-> ' WwWwWw.mysql.com '
18. Repeatrepeat (Str,count) returns a string consisting of a string of str repeating counttimes times. If Count <= 0, an empty string is returned. If STR or count is NULL, returns NULL.
19, Reversereverse (str) returns the string str that reverses the character order.
20, Insertinsert (STR,POS,LEN,NEWSTR) returns the string str, the substring starting at position POS and Len characters long substring is replaced by the string newstr. Mysql> Select INSERT (' Quadratic ', 3, 4, ' What ');-> ' quwhattic '
21, Eltelt (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.
22, Fieldfield (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.
23, Find_in_setfind_in_set (Str,strlist) if the string str is in a 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.
24, Make_setmake_set (BITS,STR1,STR2,...) returns a collection of strings consisting of substrings separated by "," characters, consisting of a string of corresponding bits in the bits collection. The str1 corresponds to bit 0,str2 corresponding to 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_setexport_set (Bits,on,off,[separator,[number_of_bits]) returns a string here for each bit in "bits", you get an "on" string, and for each reset (reset) bit, you get a "off" string. Each string is delimited with "separator" (Default ","), and only "bits" of "number_of_bits" (default 64) bits are used.
26, Lower/lcase/upper/ucaselcase (str)/lower (str): Returns the string str, changing 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. UCASE (str)/upper (str): Returns the string str, changing 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.
27, Load_fileload_file (file_name) reads in the file and returns the file contents as a string. The file must be on the server, you must specify the full path name to the file, and you must have file permissions. The file must have all the content readable and less than max_allowed_packet. If the file does not exist or because one of the above reasons cannot be read, the function returns NULL.
MySQL string common functions
