Shulou(Shulou.com)06/01 Report--

Editor to share with you what MySQL utility functions are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

MySQL function and function explanation, the management of MYSQL data will be used.

Note: the subscript of mysql starts with 1.

ASCII (str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is an empty string. Returns NULL if str is NULL. Mysql > select ASCII ('2');-> 50mysql > select ASCII (2);-> 50mysql > select ASCII ('dx');-> 100ORD (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 multibyte character. Returns the same value as the ASCII () function. Mysql > select ORD ('2');-> 50CONV

Convert numbers between different digits. Returns the string number of the number N, converted from from_base to to_base, and NULL if any parameter is NULL. The parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered to be a signed number, otherwise N is regarded as an unsigned number. CONV works at 64-bit precision.

That is, N is the data to be converted, from_base is the primitive, and to_base is the target. Mysql > select CONV ("a", 16jue 2);-> '1010'mysql > select CONV ("6e", 18pr 8);->' 172'mysql > select CONV (- 17pr 10pyrryl 18);->'- H'mysql > select CONV (10 + "10" + '10prime0xajin10);->' 40'BIN (N)

Returns a string representation of the binary value N, where N is a long integer (BIGINT) number, which is equivalent to CONV. If N is NULL, return NULL. Mysql > select BIN (12);-> '1100'OCT (N)

Returns a string representation of the octal value N, where N is a long integer number, which is equivalent to CONV. If N is NULL, return ``. Mysql > select OCT (12);-> '14'HEX (N)

Returns a string representation of the hexadecimal value N, where N is a BIGINT number, which is equivalent to CONV. If N is NULL, return NULL. Mysql > select HEX (255);-> 'FF'CHAR (NJI..)

CHAR () interprets parameters as integers and returns a string consisting of the ASCII code characters of those integers. The NULL value is skipped. Mysql > select CHAR;-> 'MySQL'mysql > select CHAR;->' MMM'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. Mysql > select CONCAT ('My',' slots, 'QL');->' MySQL'mysql > select CONCAT ('My', NULL,' QL');-> NULLmysql > select CONCAT;-> '14.3'

9.LENGTH (str), OCTET_LENGTH (str), CHAR_LENGTH (str), CHARACTER_LENGTH (str)

Returns the length of the string str.

Note that for multibyte characters, their CHAR_LENGTH () is evaluated only once.

Mysql > select LENGTH ('text');-> 4mysql > select LENGTH (' compendium');-> 6mysql > select OCTET_LENGTH ('text');-> 4mysql > select OCTET_LENGTH (' compendium');-> 6mysql > select CHAR_LENGTH ('text');-> 4mysql > select CHAR_LENGTH (' compendium');-> 2mysql > select CHARACTER_LENGTH ('text');-> 4mysql > select CHARACTER_LENGTH (' compendium') -> 2LOCATE (substr,str), POSITION (substr IN str)

Returns the position where the substring substr appears first in the string str, and returns 0 if substr is not in str. Mysql > select LOCATE ('bar',' foobarbar');-> 4mysql > select LOCATE ('xbar',' foobar');-> 0

This function is multibyte reliable.

LOCATE (substr,str,pos)

Returns the position where the substring substr appears first in the string str, starting with the position pos. If substr is not in str, return 0. Mysql > select LOCATE ('bar',' foobarbar',5);-> 7

This function is multibyte reliable.

INSTR (str,substr)

Returns the first occurrence of the substring substr in the string str. This is the same as LOCATE (), which has the form of two parameters, except that the parameters are reversed. Mysql > select INSTR ('foobarbar',' bar');-> 4mysql > select INSTR ('xbar',' foobar');-> 0LPAD (str,len,padstr)

Returns the string str, padded on the left with the string padstr until str is len characters long. Mysql > select LPAD ('hi',7,'abc');->' abcabhi'RPAD (str,len,padstr)

Returns the string str, padded on the right with the string padstr until str is len characters long. Mysql > select RPAD ('hi',7,'abc');->' hiabcab'LEFT (str,len)

Returns the leftmost len characters of the string str. Mysql > select LEFT ('foobarbar', 5);->' fooba'RIGHT (str,len)

Returns the rightmost len characters of the string str. Mysql > select RIGHT ('foobarbar', 4);->' rbar'SUBSTRING (str,pos,len), SUBSTRING (str FROM pos FOR len), MID (str,pos,len)

Returns a substring of len characters from the string str, starting with the position pos. The variant form of using FROM is the ANSI SQL92 syntax. Mysql > select SUBSTRING ('Quadratically',5,6);->' ratica'mysql > select SUBSTRING ('helloworld' FROM 2 FOR 5);->' ellow'mysql > select MID ('helloworld' FROM 2 FOR 5);->' ellow'

Note: the usage of SUBSTR is the same as SUBSTRING

SUBSTRING_INDEX (str,delim,count)

Returns the substring after the delimiter delim that appears at the count of the string str. If count is positive, returns all characters from the last delimiter to the left (the number from the left). If count is negative, returns the last delimiter to all characters on the right (from the right). Mysql > select SUBSTRING_INDEX ('www.mysql.com','.', 2);-> 'www.mysql'mysql > select SUBSTRING_INDEX (' www.mysql.com','.'- 2);-> 'mysql.com'LTRIM (str)

Returns the string str whose leading space character is deleted. Mysql > select LTRIM ('barbar');->' barbar'RTRIM (str)

Returns the string str whose trailing space character was deleted. Mysql > select RTRIM ('barbar');->' barbar'TRIM ([remstr FROM] str), TRIM ([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

Returns the string str whose remstr prefixes or suffixes are deleted. If no modifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, the space is deleted (the middle space is not deleted). Mysql > SELECT TRIM ('bar bar');->' bar bar' mysql > SELECT TRIM (LEADING'x 'FROM' xxxbarxxx');-- delete specified first character x-> 'barxxx' mysql > SELECT TRIM (BOTH' x 'FROM' xxxbarxxx');-- delete specified leading and trailing character x-> 'bar' mysql > SELECT TRIM (TRAILING' xyz' FROM 'barxxyz') -- deletes the specified trailing character x-> 'barx'SOUNDEX (str)

Returns a homonym string for str. Two strings that sound "roughly the same" should have the same homonym string. A "standard" homophone string is four characters long, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () on the result to get a "standard" homophone string. All non-numeric alphanumeric characters are ignored in the given string. All international letters outside Amurz are treated as vowels. Mysql > select SOUNDEX ('Hello');->' H400'mysql > select SOUNDEX ('Quadratically');->' Q36324'SPACE (N)

Returns a string of N space characters. Mysql > select SPACE (6);-> 'REPLACE (str,from_str,to_str)

Returns the string str where all occurrences of the string from_str are replaced by the string to_str. Mysql > select REPLACE ('www.mysql.com',' walled, 'Ww');->' WwWwWw.mysql.com'REPEAT (str,count)

Returns a string consisting of the string str that repeats countTimes times. If count select REPEAT ('MySQL', 3);->' MySQLMySQLMySQL'REVERSE (str)

Returns the string str that reverses the order of characters. Mysql > select REVERSE ('abc');->' cba'INSERT (str,pos,len,newstr)

Returns the string str, the substring at the beginning of the pos and the length of the len character is replaced by the string newstr. Mysql > select INSERT ('Quadratic', 3,4,' What');-> 'QuWhattic'ELT.

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 parameters. ELT () is the inverse operation of FIELD (). Mysql > select ELT (1, 'ej',' Heja', 'hej',' foo');-> 'ej'mysql > select ELT (4,' ej', 'Heja',' hej', 'foo');->' foo'FIELD (str,str1,str2,str3,...)

Returns str in str1, str2, str3,... The index of the list. If str is not found, 0 is returned. FIELD () is the inverse operation of ELT (). Mysql > select FIELD ('ej',' Hej', 'ej',' Heja', 'hej',' foo');-> 2mysql > select FIELD ('fo',' Hej', 'ej',' Heja', 'hej',' foo');-> 0FIND_IN_SET (str,strlist)

If the string str is in the table strlist consisting of N substrings, a value from 1 to N is returned. A string table is a string of substrings separated by ",". 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 operations! Returns 0 if str is not in strlist or if strlist is an empty string. Returns NULL if any of the parameters is NULL. If the first parameter contains a ",", the function will not work properly. Mysql > SELECT FIND_IN_SET;-> 2MAKE_SET (bits,str1,str2,...)

Returns a collection containing a string of substrings separated by "," characters, consisting of strings corresponding to bits in the bits collection. Str1 corresponds to bit 0, corresponding to bit 1, and so on. In str1, str2,... The NULL string in is not added to the result. Mysql > SELECT MAKE_SET;-> 'a'mysql > SELECT MAKE_SET';-> 'hello,world'mysql > SELECT MAKE_SET');-> 'hello,world'mysql > SELECT MAKE_SET');-> 'hello'mysql > SELECT MAKE_SET';-> 'world';-> 'world');-> 'world';-> 'world');->''

Description:

Bits should convert the period to binary, for example, 1 is 0001, and the reverse sort is 1000. Put the string str1,str2 after bits in the reverse binary sort, and take out the string corresponding to 1, and you will get hello.

| 1 | 4 means to perform or operation. It is 0001 | 0100 and gets 0101. If it is sorted upside down, it is 1010, then 'hello','nice','world' gets hello word'. What hello','nice',NULL,'world' gets is hello. NULL does not take, only 1 takes the corresponding string.

EXPORT_SET (bits,on,off, [separator, [number_of_bits]])

Returns a string, where you get a "on" string for each bit set in "bits" and a "off" string for each reset bit. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bit of "bits" is used. Mysql > select EXPORT_SET-> NLCASE (str), LOWER (str)

Returns the string str, which changes all characters to lowercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes. Mysql > select LCASE ('QUADRATICALLY');->' quadratically'UCASE (str), UPPER (str)

Returns the string str, changing all characters to uppercase based on the current character set mapping (default is ISO-8859-1 Latin1). This function is reliable for multibytes. Mysql > select UCASE ('Hello');->' HELLO'LOAD_FILE (file_name)

Reads the file and returns the contents of the file as a string. The file must be on the server, you must specify the full pathname to the file, and you must have file permission. Everything in the file must be readable and less than max_allowed_packet. If the file does not exist or cannot be read for one of the above reasons, the function returns NULL. Mysql > UPDATE table_name SET blob_column=LOAD_FILE ("/ tmp/picture") WHERE id=1;CONCAT (str1,str2,...)

Concatenates multiple strings into a single string and returns the string produced as a result of the connection parameter. If any parameter is NULL, the return value is NULL. There may be one or more parameters. If all parameters are non-binary strings, the result is a non-binary string. If the argument contains any binary string, the result is a binary string. A numeric parameter is converted to its equivalent binary string format; to avoid this, use the explicit type cast, such as SELECT CONCAT (CAST (int_col AS CHAR), char_col) mysql > SELECT CONCAT ('My','S','ql');->' MySQL'mysql > SELECT CONCAT ('My', NULL,' ql');-> NULLmysql > SELECT CONCAT (14.3);-> '14.3'

MySQL automatically converts numbers to strings if necessary, and vice versa:

Mysql > SELECT 1 + "1";-> 2mysql > SELECT CONCAT (2 test');->'2 test'CONCAT_WS (separator,str1,str2, …)

CONCAT_WS () stands for CONCAT With Separator and is a special form of CONCAT (). The first parameter is the delimiter of the other parameters. The position of the delimiter is placed between the two strings to concatenate. The delimiter can be a string or other parameters. If the delimiter is NULL, the result is NULL. Function ignores the NULL value after any delimiter arguments. Mysql > select CONCAT_WS (',', 'First name','Second name','Last Name');->' First name,Second name,Last Name'mysql > select CONCAT_WS (',', 'First name',NULL,'Last Name');->' First name,Last Name'

Note CONCAT_WS () does not ignore any empty strings. (however, all NULL are ignored).

These are all the contents of the article "what are the MySQL utility functions?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.