Numeric/Math Functions

Following are some useful numeric and math functions that are fairly universal across database platforms:

ABS(number)

Returns the absolute value of number.

CEIL(number) or CEILING(number)

Returns the smallest integer that is greater than or equal to the number that you pass. Use CEILING for SQL Server and CEIL for other platforms. Remember that with negative numbers, the greater value has the lower absolute value: CEIL(5.5) is 6, whereas CEIL(−5.5) is −5.

EXP(number)

Returns the mathematical constant e (≈2.71828183)also known as Euler’s constantraised to the power of number.

FLOOR(number)

Returns the largest integer that is less than or equal to the number you pass. Remember that with negative numbers, the lesser value has the higher absolute value: FLOOR(5.5) is 5, whereas FLOOR(−5.5) is −6.

LN(number)

Returns the natural logarithm of number. Supported in DB2, Oracle, and PostgreSQL. For other platforms, use LOG instead.

LOG(number)

Returns the natural logarithm of number (in DB2, SQL Server, and MySQL). In PostgreSQL, it returns the base-10 logarithm of number.

LOG(base , number)

Returns the logarithm of number in a base that you specify (Oracle and PostgreSQL).

LOG10(number)

Returns the base-10 logarithm of number (DB2, MySQL, and SQL Server).

MOD(top , bottom)

Returns the remainder of top divided by bottom (DB2, MySQL, Oracle, and PostgreSQL).

NANVL(value , alternate)

Returns an alternate value for any floating-point NaN (Not-a-Number) value. If value is NaN, then alternate ...

Get SQL Pocket Guide, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.