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.
MySQL String Functions
Function
Description
ASCII
Returns the ASCII code of a character
CHAR_LENGTH
Returns the number of characters in a string
CHARACTER_LENGTH
Returns the number of characters in a string
CONCAT
Combines two or more strings
CONCAT_WS
Combines strings with a separator
FIELD
Returns the index position of a value in a list
FIND_IN_SET
Finds the position of a string in a list of strings
FORMAT
Formats a number with commas and decimal places
INSERT
Inserts a substring into a string
INSTR
Returns the position of the first occurrence of a substring
LCASE
Converts a string to lowercase
LEFT
Returns the leftmost characters of a string
LENGTH
Returns the length of a string (in bytes)
LOCATE
Returns the position of a substring
LOWER
Converts a string to lowercase
LPAD
Left-pads a string
LTRIM
Removes leading spaces
MID
Extracts a substring
POSITION
Returns the position of a substring
REPEAT
Repeats a string
REPLACE
Replaces occurrences of a substring
REVERSE
Reverses a string
RIGHT
Returns the rightmost characters of a string
RPAD
Right-pads a string
RTRIM
Removes trailing spaces
SPACE
Returns a string of space characters
STRCMP
Compares two strings
SUBSTR
Extracts a substring
SUBSTRING
Extracts a substring
SUBSTRING_INDEX
Returns a substring before the nth occurrence of a delimiter
TRIM
Removes leading and trailing spaces
UCASE
Converts a string to uppercase
UPPER
Converts a string to uppercase
MySQL Numeric Functions
Function
Description
ABS
Returns the absolute value
ACOS
Returns arc cosine
ASIN
Returns arc sine
ATAN
Returns arc tangent of one or two numbers
ATAN2
Returns arc tangent of two numbers
AVG
Returns the average value
CEIL
Returns smallest integer >= number
CEILING
Returns smallest integer >= number
COS
Returns cosine
COT
Returns cotangent
COUNT
Returns number of records in result set
DEGREES
Converts radians to degrees
DIV
Performs integer division
EXP
Returns e^x
FLOOR
Returns largest integer <= number
GREATEST
Returns the greatest value from arguments
LEAST
Returns the smallest value from arguments
LN
Returns natural logarithm
LOG
Returns logarithm (natural or specified base)
LOG10
Returns logarithm base 10
LOG2
Returns logarithm base 2
MAX
Returns maximum value
MIN
Returns minimum value
MOD
Returns remainder of division
PI
Returns value of pi
POW
Returns power of number
POWER
Returns power of number
RADIANS
Converts degrees to radians
RAND
Returns a random number
ROUND
Rounds a number
SIGN
Returns the sign of a number
SIN
Returns sine
SQRT
Returns square root
SUM
Returns sum of values
TAN
Returns tangent
TRUNCATE
Truncates a number
MySQL Date Functions
Function
Description
ADDDATE
Adds interval to date
ADDTIME
Adds interval to time/datetime
CURDATE
Returns current date
CURRENT_DATE
Returns current date
CURRENT_TIME
Returns current time
CURRENT_TIMESTAMP
Returns current date and time
CURTIME
Returns current time
DATE
Extracts date part from datetime
DATEDIFF
Returns days between two dates
DATE_ADD
Adds interval to date
DATE_FORMAT
Formats a date
DATE_SUB
Subtracts interval from date
DAY
Returns day of month
DAYNAME
Returns name of weekday
DAYOFMONTH
Returns day of month
DAYOFWEEK
Returns weekday index
DAYOFYEAR
Returns day of year
EXTRACT
Extracts part of a date
FROM_DAYS
Returns a date from numeric value
HOUR
Returns hour part of time
LAST_DAY
Returns last day of month
LOCALTIME
Returns current date and time
LOCALTIMESTAMP
Returns current date and time
MAKEDATE
Returns a date from year and day number
MAKETIME
Returns a time from hour, minute, second
MICROSECOND
Returns microsecond part of time
MINUTE
Returns minute part of time
MONTH
Returns month part of date
MONTHNAME
Returns name of month
NOW
Returns current date and time
PERIOD_ADD
Adds months to a period
PERIOD_DIFF
Returns difference between two periods
QUARTER
Returns quarter of year
SECOND
Returns seconds part of time
SEC_TO_TIME
Converts seconds to time
STR_TO_DATE
Converts string to date
SUBDATE
Subtracts interval from date
SUBTIME
Subtracts interval from time
SYSDATE
Returns current date and time
TIME
Extracts time part from datetime
TIME_FORMAT
Formats time
TIME_TO_SEC
Converts time to seconds
TIMEDIFF
Returns difference between two times
TIMESTAMP
Returns datetime value
TO_DAYS
Returns number of days since ‘0000-00-00’
WEEK
Returns week number
WEEKDAY
Returns weekday number
WEEKOFYEAR
Returns week number of year
YEAR
Returns year part of date
YEARWEEK
Returns year and week number
MySQL Advanced Functions
Function
Description
BIN
Returns binary representation of a number
BINARY
Converts value to binary string
CASE
Performs conditional logic
CAST
Converts value to a specified data type
COALESCE
Returns first non-null value
CONNECTION_ID
Returns unique connection ID
CONV
Converts number between numeric bases
CONVERT
Converts value to specified data type or character set
CURRENT_USER
Returns current authenticated user
DATABASE
Returns current database name
IF
Returns value based on condition
IFNULL
Returns specified value if expression is NULL
ISNULL
Returns 1 or 0 based on whether expression is NULL