Data Manipulation   «Prev  Next»

Lesson 5 Number functions: TO_CHAR, ROUND, TRUNC
Objective Interpret the effect of the number functions TO_CHAR, ROUND, and TRUNC.

TO_CHAR, ROUND, TRUNC

Number Functions: TO_CHAR, ROUND, TRUNC

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.
SELECT 'You spent' 
|| TO_CHAR(TOTAL_SALE_AMOUNT) 
||'last month.' VALUED_CUSTOMER
FROM CUSTOMER_SALE

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.

1) The query shows the value stored in the TOTAL_SALE_AMOUNT column in the first results column labeled ORIG. The second column is the TOTAL_SALE_AMOUNT plus 10% for shipping and handling. Notice that in some cases, the number has three digits after the decimal point. The third column is the second column with the ROUND function applied to round to two places after the decimal place.

2) This query shows the calculated value in the first column (same as before: TOTAL_SALE_AMOUNT plus 10%). In the next column, you see the round value as before. The third column shows the calculated value with the TRUNC function applied to truncate to two places after the decimal point.

3) This query changes both the ROUND and TRUNC parameter from 2 to 0, so that each calculated value is rounded and truncated to a whole number.

4) This final query changes the ROUND and TRUNC parameter from zero to -2, which rounds and truncates to the hundreds (two decimal places to the left of the decimal).


Round Trunc Functions
In the next lesson, you will learn how to identify valid date-related date functions and Oracle's standard date format.