MySQL Functions – References

MySQL has many built-in functions.

This reference contains string, numeric, date, and some advanced functions in MySQL.

MySQL String Functions

FunctionDescription
ASCIIReturns the ASCII value for the specific character
CHAR_LENGTHReturns the length of a string (in characters)
CHARACTER_LENGTHReturns the length of a string (in characters)
CONCATAdds two or more expressions together
CONCAT_WSAdds two or more expressions together with a separator
FIELDReturns the index position of a value in a list of values
FIND_IN_SETReturns the position of a string within a list of strings
FORMATFormats a number to a format like “#,###,###.##”, rounded to a specified number of decimal places
INSERTInserts a string within a string at the specified position and for a certain number of characters
INSTRReturns the position of the first occurrence of a string in another string
LCASEConverts a string to lower-case
LEFTExtracts a number of characters from a string (starting from left)
LENGTHReturns the length of a string (in bytes)
LOCATEReturns the position of the first occurrence of a substring in a string
LOWERConverts a string to lower-case
LPADLeft-pads a string with another string, to a certain length
LTRIMRemoves leading spaces from a string
MIDExtracts a substring from a string (starting at any position)
POSITIONReturns the position of the first occurrence of a substring in a string
REPEATRepeats a string as many times as specified
REPLACEReplaces all occurrences of a substring within a string, with a new substring
REVERSEReverses a string and returns the result
RIGHTExtracts a number of characters from a string (starting from right)
RPADRight-pads a string with another string, to a certain length
RTRIMRemoves trailing spaces from a string
SPACEReturns a string of the specified number of space characters
STRCMPCompares two strings
SUBSTRExtracts a substring from a string (starting at any position)
SUBSTRINGExtracts a substring from a string (starting at any position)
SUBSTRING_INDEXReturns a substring of a string before a specified number of delimiter occurs
TRIMRemoves leading and trailing spaces from a string
UCASEConverts a string to upper-case
UPPERConverts a string to upper-case

MySQL Numeric Functions

FunctionDescription
ABSReturns the absolute value of a number
ACOSReturns the arc cosine of a number
ASINReturns the arc sine of a number
ATANReturns the arc tangent of one or two numbers
ATAN2Returns the arc tangent of two numbers
AVGReturns the average value of an expression
CEILReturns the smallest integer value that is >= to a number
CEILINGReturns the smallest integer value that is >= to a number
COSReturns the cosine of a number
COTReturns the cotangent of a number
COUNTReturns the number of records returned by a select query
DEGREESConverts a value in radians to degrees
DIVUsed for integer division
EXPReturns e raised to the power of a specified number
FLOORReturns the largest integer value that is <= to a number
GREATESTReturns the greatest value of the list of arguments
LEASTReturns the smallest value of the list of arguments
LNReturns the natural logarithm of a number
LOGReturns the natural logarithm of a number, or the logarithm of a number to a specified base
LOG10Returns the natural logarithm of a number to base 10
LOG2Returns the natural logarithm of a number to base 2
MAXReturns the maximum value in a set of values
MINReturns the minimum value in a set of values
MODReturns the remainder of a number divided by another number
PIReturns the value of PI
POWReturns the value of a number raised to the power of another number
POWERReturns the value of a number raised to the power of another number
RADIANSConverts a degree value into radians
RANDReturns a random number
ROUNDRounds a number to a specified number of decimal places
SIGNReturns the sign of a number
SINReturns the sine of a number
SQRTReturns the square root of a number
SUMCalculates the sum of a set of values
TANReturns the tangent of a number
TRUNCATETruncates a number to the specified number of decimal places

MySQL Date Functions

FunctionDescription
ADDDATEAdds a time/date interval to a date and then returns the date
ADDTIMEAdds a time interval to a time/datetime and then returns the time/datetime
CURDATEReturns the current date
CURRENT_DATEReturns the current date
CURRENT_TIMEReturns the current time
CURRENT_TIMESTAMPReturns the current date and time
CURTIMEReturns the current time
DATEExtracts the date part from a datetime expression
DATEDIFFReturns the number of days between two date values
DATE_ADDAdds a time/date interval to a date and then returns the date
DATE_FORMATFormats a date
DATE_SUBSubtracts a time/date interval from a date and then returns the date
DAYReturns the day of the month for a given date
DAYNAMEReturns the weekday name for a given date
DAYOFMONTHReturns the day of the month for a given date
DAYOFWEEKReturns the weekday index for a given date
DAYOFYEARReturns the day of the year for a given date
EXTRACTExtracts a part from a given date
FROM_DAYSReturns a date from a numeric datevalue
HOURReturns the hour part for a given date
LAST_DAYExtracts the last day of the month for a given date
LOCALTIMEReturns the current date and time
LOCALTIMESTAMPReturns the current date and time
MAKEDATECreates and returns a date based on a year and a number of days value
MAKETIMECreates and returns a time based on an hour, minute, and second value
MICROSECONDReturns the microsecond part of a time/datetime
MINUTEReturns the minute part of a time/datetime
MONTHReturns the month part for a given date
MONTHNAMEReturns the name of the month for a given date
NOWReturns the current date and time
PERIOD_ADDAdds a specified number of months to a period
PERIOD_DIFFReturns the difference between two periods
QUARTERReturns the quarter of the year for a given date value
SECONDReturns the seconds part of a time/datetime
SEC_TO_TIMEReturns a time value based on the specified seconds
STR_TO_DATEReturns a date based on a string and a format
SUBDATESubtracts a time/date interval from a date and then returns the date
SUBTIMESubtracts a time interval from a datetime and then returns the time/datetime
SYSDATEReturns the current date and time
TIMEExtracts the time part from a given time/datetime
TIME_FORMATFormats a time by a specified format
TIME_TO_SECConverts a time value into seconds
TIMEDIFFReturns the difference between two time/datetime expressions
TIMESTAMPReturns a datetime value based on a date or datetime value
TO_DAYSReturns the number of days between a date and date “0000-00-00”
WEEKReturns the week number for a given date
WEEKDAYReturns the weekday number for a given date
WEEKOFYEARReturns the week number for a given date
YEARReturns the year part for a given date
YEARWEEKReturns the year and week number for a given date

MySQL Advanced Functions

FunctionDescription
BINReturns a binary representation of a number
BINARYConverts a value to a binary string
CASEGoes through conditions and return a value when the first condition is met
CASTConverts a value (of any type) into a specified datatype
COALESCEReturns the first non-null value in a list
CONNECTION_IDReturns the unique connection ID for the current connection
CONVConverts a number from one numeric base system to another
CONVERTConverts a value into the specified datatype or character set
CURRENT_USERReturns the user name and host name for the MySQL account that the server used to authenticate the current client
DATABASEReturns the name of the current database
IFReturns a value if a condition is TRUE, or another value if a condition is FALSE
IFNULLReturn a specified value if the expression is NULL, otherwise return the expression
ISNULLReturns 1 or 0 depending on whether an expression is NULL
LAST_INSERT_IDReturns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table
NULLIFCompares two expressions and returns NULL if they are equal. Otherwise, the first expression is returned
SESSION_USERReturns the current MySQL user name and host name
SYSTEM_USERReturns the current MySQL user name and host name
USERReturns the current MySQL user name and host name
VERSIONReturns the current version of the MySQL database