MySQL Numeric Functions

Numeric value functions can take a variety of data types as input, but the output is always a numeric value. 

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of a number or the arc tangent of n and m
ATAN2 Returns the arc tangent of n and m
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is greater than or equal to a number
CEILING Returns the smallest integer value that is greater than or equal to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records in a select query
DEGREES Converts a radian value into degrees
DIV Used for integer division
EXP Returns e raised to the power of number
FLOOR Returns the largest integer value that is less than or equal to a number
GREATEST Returns the greatest value in a list of expressions
LEAST Returns the smallest value in a list of expressions
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number or the logarithm of a number to a specified base
LOG10 Returns the base-10 logarithm of a number
LOG2 Returns the base-2 logarithm of a number
MAX Returns the maximum value of an expression
MIN Returns the minimum value of an expression
MOD Returns the remainder of n divided by m
PI Returns the value of PI displayed with 6 decimal places
POW Returns m raised to the nth power
POWER Returns m raised to the nth power
RADIANS Converts a value in degrees to radians
RAND Returns a random number or a random number within a range
ROUND Returns a number rounded to a certain number of decimal places
SIGN Returns a value indicating the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Returns the summed value of an expression
TAN Returns the tangent of a number
TRUNCATE Returns a number truncated to a certain number of decimal places

Aggregate Functions

When dealing with a set of table rows, often what you want to know is some aggregate property that applies to the whole set. SQL has five such aggregate or set functions: COUNT, AVG, MAX, MIN, and SUM.

COUNT

The COUNT function returns the number of rows in a table, or the number of rows that meet a specified condition.

AVG

The AVG function calculates and returns the average of the values in the specified column. It works only on columns that contain numeric data.

MAX

The MAX function returns the maximum value found in the specified column.

MIN

The MIN function gives you the minimum value found in the specified column.

Numeric Functions

POSITION

POSITION searches for a specified target string within a specified source string and returns the character position where the target string begins.

POSITION (target IN source)

If the function doesn't find the target string, the POSITION function returns a zero value. If the target string has zero length, the POSITION function always returns a value of 1. If any operand in the function has a null value, the result is a null value.

EXTRACT

The EXTRACT function extracts a single field from a datetime or an interval.

CHARACTER_LENGTH

The CHARACTER_LENGTH function returns the number of characters in a character string.

ABS

The ABS function returns the absolute value of a numeric value expression.

TRIGONOMETRIC FUNCTIONS

The trig functions give you the values you would expect, such as the sine of an angle or the hyperbolic tangent.

LOGARITHMIC FUNCTIONS

The logarithmic functions enable you to generate the logarithm of a number, either a base-10 logarithm, a natural logarithm, or a logarithm to a base that you specify.

MOD

The MOD function returns the modulus, the remainder of division of one number by another, of two numeric value expressions.

EXP

This function raises the base of the natural logarithms e to the power specified by a numeric value expression.

POWER

This function raises the value of the first numeric value expression to the power of the second numeric value expression.

SQRT

This function returns the square root of the value of the numeric value expression.

FLOOR

This function rounds the numeric value expression to the largest integer not greater than the expression.

CEIL

This function rounds the numeric value expression to the smallest integer not less than the expression.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive