RelationalDBDesign RelationalDBDesign

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.

## 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.
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.
The following SlideShow shows a query that lists a numbers with variations on the `ROUND` function and the `TRUNC` function.