mysql--function

function

A number of built-in functions are available in MySQL, such as:

Char_length (StrThe 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. For one containing five two-byte character sets, the LENGTH () return value isTen, and the return value of Char_length () is 5.        CONCAT (STR1,STR2,...) string concatenation if any one of the arguments is NULL, the return value isNULL.        Concat_ws (SEPARATOR,STR1,STR2,...) string concatenation (custom connector) Concat_ws () does not ignore any empty strings. (However, it ignores allNULL). CONV (n,from_base,to_base) binary conversions such as:SELECTCONV ('a', -,2); Represents the conversion of a from 16 to a 2 binary string representation of format (X,D) that formats the number X as'#,###,###.##', the D-bit is retained after the decimal point in a rounded manner, and the result is returned as a string. If D is0, the result is returned without a decimal point, or with no fractional parts. For example:SELECTFORMAT (12332.1,4); The result is:'12,332.1000'    INSERT(StrPosLen, Newstr) inserts the string pos at the specified location in str: to replace the position in the locationLen: Replacement length NEWSTR: New String Special: If the Pos exceeds the original string length, the original string is returned if Len exceeds the original string length, then the new string is completely replaced INST R (Str, substr) returns a stringStrthe first occurrence of a neutron string.  Left(Str,Lenreturns the substring character of the string Str from the beginning of the Len position. LOWER(Str) Variable lowercaseUPPER(Str) to capitalizeLTRIM(Str) returns a stringStr, its leading space character is deleted. RTRIM(Str) returns a stringStr, the trailing space character is deleted. SUBSTRING(StrPosLen) Gets the string subsequence LOCATE (substr,Str, POS) gets the sub-sequence index position REPEAT (Str,Countreturns a string consisting of a repeating string str, the number of which is equal to count. IfCount <= 0, an empty string is returned. If STR orCountForNULL, The returnNULL. REPLACE(Str, From_str,to_str) returns the string str and all string from_str that are substituted by the string to_str. REVERSE(Str) returns a stringStr, in reverse order and character order.  Right(Str,Lenstarts from the string str and returns a subsequence of Len characters from behindSPACE(n) returns a string consisting of N spaces. SUBSTRING(Str, POS),SUBSTRING(Str  fromPosSUBSTRING(StrPosLen) ,SUBSTRING(Str  fromPos for Lenthe format without the Len parameter returns a substring from the string str, starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.        You can use a negative value for the POS in the following format function. MySQL> SELECT SUBSTRING('quadratically',5);  - 'ratically'MySQL> SELECT SUBSTRING('Foobarbar'  from 4);  - 'Barbar'MySQL> SELECT SUBSTRING('quadratically',5,6);  - 'Ratica'MySQL> SELECT SUBSTRING('Sakila',-3);  - 'ila'MySQL> SELECT SUBSTRING('Sakila',-5,3);  - 'Aki'MySQL> SELECT SUBSTRING('Sakila'  from -4  for 2);  - 'ki'TRIM ([{BOTH | Leading | TRAILING} [Remstr]  from]Str) TRIM (remstr from]Str) returns a stringStr, where all remstr prefixes and/or suffix has been deleted. If none of the classifier both, leadin, or trailing is given, it is assumed to be both.        REMSTR is optional and can be removed without specifying a space. MySQL> SELECTTRIM ('Bar');  - 'Bar'MySQL> SELECTTRIM (Leading'x'  from 'xxxbarxxx');  - 'barxxx'MySQL> SELECTTRIM (BOTH'x'  from 'xxxbarxxx');  - 'Bar'MySQL> SELECTTRIM (TRAILING'XYZ'  from 'barxxyz');  - 'Barx'partially built-in functions

function1. Custom Functions

delimiter \ Create function F1 (    int,    int)returnsintBEGIN     declareint;     Set = + i2;     return (num); END \delimiter;

2. Delete a function

Drop function func_name;

3. Execution function

--Get return valueDeclare @i VARCHAR( +);Select UPPER('Alex') into @i;SELECT @i;--using in QueriesSelectF1 ( One, nid), name fromTB2;

mysql--function