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
)
orCEILING(
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)
is6
, whereasCEIL(−5.5)
is−5
.EXP(
number
)
Returns the mathematical constant e (≈2.71828183)—also known as Euler’s constant—raised 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)
is5
, whereasFLOOR(−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 ofnumber
.LOG(
base
,
number
)
Returns the logarithm of
number
in abase
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 bybottom
(DB2, MySQL, Oracle, and PostgreSQL).NANVL(
value
,
alternate
)
Returns an alternate value for any floating-point NaN (Not-a-Number)
value
. Ifvalue
is NaN, thenalternate ...
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.