MySQL Functions

MySQL offers many built-in functions to help process and analyze your data. These functions cover string manipulation, numeric operations, date handling, and advanced logic.

Tutorials dojo strip

MySQL String Functions

FunctionDescription
ASCIIReturns the ASCII code of a character
CHAR_LENGTHReturns the number of characters in a string
CHARACTER_LENGTHReturns the number of characters in a string
CONCATCombines two or more strings
CONCAT_WSCombines strings with a separator
FIELDReturns the index position of a value in a list
FIND_IN_SETFinds the position of a string in a list of strings
FORMATFormats a number with commas and decimal places
INSERTInserts a substring into a string
INSTRReturns the position of the first occurrence of a substring
LCASEConverts a string to lowercase
LEFTReturns the leftmost characters of a string
LENGTHReturns the length of a string (in bytes)
LOCATEReturns the position of a substring
LOWERConverts a string to lowercase
LPADLeft-pads a string
LTRIMRemoves leading spaces
MIDExtracts a substring
POSITIONReturns the position of a substring
REPEATRepeats a string
REPLACEReplaces occurrences of a substring
REVERSEReverses a string
RIGHTReturns the rightmost characters of a string
RPADRight-pads a string
RTRIMRemoves trailing spaces
SPACEReturns a string of space characters
STRCMPCompares two strings
SUBSTRExtracts a substring
SUBSTRINGExtracts a substring
SUBSTRING_INDEXReturns a substring before the nth occurrence of a delimiter
TRIMRemoves leading and trailing spaces
UCASEConverts a string to uppercase
UPPERConverts a string to uppercase

MySQL Numeric Functions

FunctionDescription
ABSReturns the absolute value
ACOSReturns arc cosine
ASINReturns arc sine
ATANReturns arc tangent of one or two numbers
ATAN2Returns arc tangent of two numbers
AVGReturns the average value
CEILReturns smallest integer >= number
CEILINGReturns smallest integer >= number
COSReturns cosine
COTReturns cotangent
COUNTReturns number of records in result set
DEGREESConverts radians to degrees
DIVPerforms integer division
EXPReturns e^x
FLOORReturns largest integer <= number
GREATESTReturns the greatest value from arguments
LEASTReturns the smallest value from arguments
LNReturns natural logarithm
LOGReturns logarithm (natural or specified base)
LOG10Returns logarithm base 10
LOG2Returns logarithm base 2
MAXReturns maximum value
MINReturns minimum value
MODReturns remainder of division
PIReturns value of pi
POWReturns power of number
POWERReturns power of number
RADIANSConverts degrees to radians
RANDReturns a random number
ROUNDRounds a number
SIGNReturns the sign of a number
SINReturns sine
SQRTReturns square root
SUMReturns sum of values
TANReturns tangent
TRUNCATETruncates a number

MySQL Date Functions

FunctionDescription
ADDDATEAdds interval to date
ADDTIMEAdds interval to time/datetime
CURDATEReturns current date
CURRENT_DATEReturns current date
CURRENT_TIMEReturns current time
CURRENT_TIMESTAMPReturns current date and time
CURTIMEReturns current time
DATEExtracts date part from datetime
DATEDIFFReturns days between two dates
DATE_ADDAdds interval to date
DATE_FORMATFormats a date
DATE_SUBSubtracts interval from date
DAYReturns day of month
DAYNAMEReturns name of weekday
DAYOFMONTHReturns day of month
DAYOFWEEKReturns weekday index
DAYOFYEARReturns day of year
EXTRACTExtracts part of a date
FROM_DAYSReturns a date from numeric value
HOURReturns hour part of time
LAST_DAYReturns last day of month
LOCALTIMEReturns current date and time
LOCALTIMESTAMPReturns current date and time
MAKEDATEReturns a date from year and day number
MAKETIMEReturns a time from hour, minute, second
MICROSECONDReturns microsecond part of time
MINUTEReturns minute part of time
MONTHReturns month part of date
MONTHNAMEReturns name of month
NOWReturns current date and time
PERIOD_ADDAdds months to a period
PERIOD_DIFFReturns difference between two periods
QUARTERReturns quarter of year
SECONDReturns seconds part of time
SEC_TO_TIMEConverts seconds to time
STR_TO_DATEConverts string to date
SUBDATESubtracts interval from date
SUBTIMESubtracts interval from time
SYSDATEReturns current date and time
TIMEExtracts time part from datetime
TIME_FORMATFormats time
TIME_TO_SECConverts time to seconds
TIMEDIFFReturns difference between two times
TIMESTAMPReturns datetime value
TO_DAYSReturns number of days since ‘0000-00-00’
WEEKReturns week number
WEEKDAYReturns weekday number
WEEKOFYEARReturns week number of year
YEARReturns year part of date
YEARWEEKReturns year and week number

MySQL Advanced Functions

FunctionDescription
BINReturns binary representation of a number
BINARYConverts value to binary string
CASEPerforms conditional logic
CASTConverts value to a specified data type
COALESCEReturns first non-null value
CONNECTION_IDReturns unique connection ID
CONVConverts number between numeric bases
CONVERTConverts value to specified data type or character set
CURRENT_USERReturns current authenticated user
DATABASEReturns current database name
IFReturns value based on condition
IFNULLReturns specified value if expression is NULL
ISNULLReturns 1 or 0 based on whether expression is NULL
LAST_INSERT_IDReturns last AUTO_INCREMENT id inserted
NULLIFReturns NULL if two expressions are equal
SESSION_USERReturns current MySQL session user
SYSTEM_USERReturns current MySQL system user
USERReturns current MySQL user
VERSIONReturns current MySQL server version

Tutorials dojo strip