Interpret the effect of the number functions TO_CHAR, ROUND, and TRUNC.
Number Functions: TO_CHAR, ROUND, TRUNC
Mathematical functions
Oracle has added a whole range of mathematical functions that you can use with numbers. The table below lists most of them.
Function
Syntax
Description
ABS
ABS(col1)
Absolute value
ACOS, ASIN, ATAN
ACOS(col1)
Arc cosine, arc sine, and arc tangent
ATAN2
ATAN2(col1, col2)
Arc tangent of col1 and col2
CEIL
CEIL(col1)
Smallest integer greater than or equal to col1
COS, SIN, TAN
COS(col1)
Cosine, sine, tangent
COSH, SINH, TANH
COSH(col1)
Hyperbolic cosine, hyperbolic sine, and hyperbolic tangent
EXP
EXP(col1)
Constant e raised to col1 power. E= 2.71828183 ...
FLOOR
FLOOR(col1)
Largest integer less than or equal to col1
LN
LN(col1)
Natural logarithm
LOG
LOG(basen, col1)
Logarithm in basen of col1
MOD
MOD(col1, col2)
Remainder of col1 divided by col2
POWER
POWER(col1, powern)
Col1 raised to powern
ROUND
ROUND(col1, placen)
Col1 is rounded to placen decimal places. Round to tens using -1 as placen.
SIGN
SIGN(col1)
Sign of col1; returns -1, 0, or +1.
SQRT
SQRT(col1)
Square root of col1
TO_CHAR
TO_CHAR(col1)
Convert col1 to a character string.
TRUNC
TRUNC(col1, placen)
Truncate col1 to placen decimal places.
The next subsections illustrate a few of the more commonly used number functions.
TO_CHAR
Sometimes you need to concatenate a number to some character field. To do so, you must first convert the number field into an equivalent
character string. Use TO_CHAR to do this as shown in the example below.
ROUND
Rounding can be useful when you have calculated data that must be rounded before storing in the database. For example, if you calculate
shipping and handling as 10 percent of the sale, you might end up with a tenth of a cent in your calculation. To adjust this, use the ROUND function.
When you insert or update number data, Oracle accepts data with too many decimal places and automatically rounds the data to the correct
number of decimal places. Oracle does not give you an error message, so you may not realize what happened until you display the stored data later.
TRUNC
Truncating and rounding are similar functions and use similar parameters. The difference, as you know, is how numbers are handled.
The following SlideShow shows a query that lists a numbers with variations on the ROUND function and the TRUNC function.