MySQL built-in functions

1. ASCII (str) returns the ASCII code of the string, such as: The return value of select ASCII (' a ') is 97;

2. The first of the ORD (str) string str sentence is a single-byte return with the same value returned by the ASCII () function.

   For example: The return value of select ORD (' 0 ') is 48

3. CONV (N,from_base,to_base)

Converts the number n into a string return (NULL is returned if any argument is null, the binary range is 2-36, and n is the unsigned number if to_base is negative), CONV works with 64-bit precision) such as: Select CONV (10,10,2) The return value is 1010

4. BIN (n) turns n into binary value and returns as String: The return value of Select Bin (10) is 1010

5. OCT (n) turns N to octal value and returns as String: The return value of the Select OCT (10) is 12

6. Hex (n) turns N to Hex and returns as a string: The return value of select HEX (10) is a

7.CHAR (n,...) is returned by the parameter n,... A string of corresponding ASCII code characters (the parameter is n,... is a sequence of numbers, null values are skipped) such as: The return value of SELECT CHAR (77,121,83,81, ' 76 ') is MySQL

8. CONCAT (str1,str2,...) The parameter is linked to a long string and returned (null if any argument is null)

such as: The return value of SELECT CONCAT (' j ', ' G ', ' C ') is JGC

9. LENGTH (str) octet_length (str) char_length (str) character_length (str )

Returns the length of the string str (only once for multibyte character char_length) such as: The return value of SELECT LENGTH (' JGC ') is 3

LOCATE (Substr,str,pos)

Returns the position of the first occurrence of the string substr in the POS position of the string str (str does not contain substr when it returns 0)

For example: The return value of SELECT LOCATE (' GC ', ' JGC ', 1) is 2

INSTR (STR,SUBSTR)

Returns the string substr the first occurrence of the string str (str returns 0 if it does not contain substr)

such as: SELECT INSTR (' Hello ', ' lo '), the return value is 4

Rpad (STR,LEN,PADSTR)

Fills the right end of Str with a string padstr until the string length is Len and returns

For example: The return value of SELECT rpad (' JGC ', 4, ' Q ') is JGCQ

Left (Str,len)

Returns the left-hand len of the string str such as: The return value of SELECT left (' JGC ', 2) is JG

Right (Str,len)

Returns the right-side Len character of a string str

A. SUBSTRING (Str,pos,len)

SUBSTRING (str from POS for len) MID (Str,pos,len)

Returns the position of the string str at POS from Len characters (the ugly syntax using from IS ANSI SQL92 standard)

SUBSTRING (Str,pos) SUBSTRING (str from POS)

Returns a substring of the position of the string str from POS

Substring_index (Str,delim,count)

Returns the substring from the count of the string str that appears after the delimiter Delim (the left end is returned when Count is positive, otherwise the right terminal string is returned)

such as: The return value of SELECT substring_index (' jgc,is,a,kind,people ', ', ',-2) is Kind,people

LTRIM (str)

Returns the string with the left space removed str

RTRIM (str)

Returns the string with the right space removed str

TRIM ([[BOTH | Leading | TRAILING] [REMSTR] from] str) returns the prefix or suffix remstr the deleted string str (position parameter default Both,remstr default value is a space)

21.SOUNDEX (str)

Returns a similar string of str (sounds "Roughly the same" string with the same sound string, non-alphanumeric characters are ignored, letters outside A-Z are used as vowels) (not understood)

22.SPACE (N)

Returns a string consisting of n space characters

. REPLACE (STR,FROM_STR,TO_STR)

Replaces substrings in string str with string To_str from_str and returns

24.REPEAT (Str,count)

Returns a string that is connected by Count string str (any parameter is null when return null,count<=0 when returning an empty string

REPEAT (Str,count)

Returns a string that is connected by Count string str (any parameter is null when return null,count<=0 when returning an empty string

INSERT (STR,POS,LEN,NEWSTR)

The string str is replaced with a string newstr by a substring of position pos, Len characters long, and returns

N,STR1,STR2,STR3 ELT (,...)

Returns the nth string (n is less than 1 or greater than the number of arguments returns NULL)

FIELD (STR,STR1,STR2,STR3,...)

Returns the ordinal of the nth string after str equals (if STR is not found returns 0)

Time_to_sec (time) returns the time value of how many seconds

Sec_to_time (seconds)

A time value that returns the number of seconds in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)

For example: The return value of SELECT Sec_to_time (30) is 00:00:30

Now() sysdate ()

Current_timestamp ()

Returns the current datetime in ' Yyyy-mm-dd HH:MM:SS ' or YYYYMMDDHHMMSS format (a string or number based on the context in which the return value is located)

curtime () current_time ()

Returns the current time value in ' HH:MM:SS ' or HHMMSS format (a string or number based on the context in which the return value is located)

curdate () current_date ()

Returns the current date value in ' Yyyy-mm-dd ' or YYYYMMDD format (a string or number based on the context in which the return value is located)

date_format (Date,format) formatting a DATE value from the format string

35.to_days (date)

Return date dates is 00 to present how many days (not calculated before 1582)

Date_add (Date,interval expr type) date_sub (date,interval expr type) adddate (date,interval expr type) subdate (date,in Terval expr type) to add and subtract date time

Panax Period_diff (P1,P2)

Returns the number of months between P1 and P2 (format yymm or yyyymm for P1 and P2) such as: The return value of SELECT Period_diff (199210,199212) is-2

Period_add (P,n)

Add n months to a period p and return (P's format yymm or yyyymm), such as Select Period_add (199210,2), the return value is 199212

SECOND (Time)

Returns the number of seconds in time (range 0 to 59)

MINUTE (Time)

Number of minutes to return time (range 0 to 59)

HOUR (Time)

Number of hours to return time (range 0 to 23)

Year (date)

Returns the date of the year (in the range 1000 to 9999)

WEEK (Date,first)

Return date is the week ordinal of the year (first default 0,first value of 1 indicates that Monday is the beginning of the week, 0 starting from Sunday)

For example: The return value of SELECT WEEK (' 2015-8-21 ') is 33

QUARTER (date)

Returns the first quarter of a year for date

MONTHNAME (date)

Returns a date that is a few months (returned by English name)

Dayname (date)

Returns the day of the week (returned by English name)

MONTH (date)

Returns the month value in date

DayOfYear (date)

Return date is the day of the year (within the range of 1 to 366)

DayOfMonth (date)

Return date is the day of the January (within the range of 1 to 31)

WEEKDAY (date)

The return date is the day of the week (0= Monday, 1 = Tuesday, 6 = Sunday)

Wuyi DAYOFWEEK (date)

The return date is the day of the week (1= Sunday, 2 = Monday, 7 = Saturday, ODBC standard)

greatest (x, y,...)

Returns the maximum value (the remaining same as least ()) such as: SELECT Greatest (12,32,45) returns 45

LEAST (x, y,...)

Returns the minimum value if the return value is used in an integer (real or size-sensitive string) context or all parameters are integers (real or size-sensitive words

string) They are compared as integers (real or size-sensitive strings), otherwise by the case-insensitive string.

TRUNCATE (n,d)

Retains the D decimal number of the digit N and returns

55.RADIANS (N)

Transform n from angle to radians and return

DEGREES (N)

Transforms n from radians to angles and returns

$ rand () rand (N)

Returns a random floating-point value within a range of 0 to 1.0 (you can use the number n as the initial value)

58.COT (N)

Returns the cotangent of X

ATAN2 (x, y)

Returns the inverse tangent of 2 variables x and Y (similar to the y/x tangent, the symbol determines the quadrant

ATAN (N)

Returns the inverse tangent value of n

ASIN (N)

Returns n Inverse sine value

ACOS (N)

Returns the n inverse cosine (n is the cosine value, in the range 1 to 1, otherwise null is returned

A. TAN (N)

Returns the tangent value of n

64.SIN (N)

Returns the sinusoidal value of n

COS (N)

Returns the cosine value of n

Pi () returns PI

• POW (x, y) power (x, y) return value X y power SQRT (N)

Returns the square root of a non-negative n

LOG10 (N)

Returns the logarithm of n at base 10

69.LOG (N)

Returns the natural logarithm of n

. EXP (N)

Returns the n-th square of the value E (the base of the natural logarithm

ROUND (N,D)

Returns the rounding value of N, leaving the D decimal number (the default value of D is 0)

CEILING (N)

Returns the smallest integer value not less than n

Floor (N)

Returns the maximum integer value not greater than n

A. MOD (n,m)

Modulo operation, returns the remainder of n being removed by M (same as% operator)

75.SIGN (N)

Returns the symbol for the parameter (1, 0, or 1)

ABS (N)

Returns the absolute value of n

load_file (file_name)

Reads the file and returns the file contents as a string (the file cannot be found, the path is incomplete, there is no permission, the length is greater than

UCASE (str) UPPER (str)

Returns the uppercase String str

LCASE (str) LOWER (str)

Returns the lowercase string str

Export_set (Bits,on,off,[separator,[number_of_bits]) sets the set of strings by bits, inserting off (separator default, ",") only if the bit equals 1 o'clock insert string on. Number_of_bits parameter is not used longer than 0 for long truncation)

Bayi. Make_set (bits,str1,str2,...)

Convert the number of the parameter 1 to binary, if the bits of a position equals 1, the string of the corresponding position is selected in the string set and returned (the null string is not added to the result)

82.find_in_set (Str,strlist)

Returns the ordinal of STR in the string set Strlist (any parameter is NULL, returns NULL if STR does not find return 0, parameter 1 contains "," when working abnormally)

MySQL built-in functions