Lesson 5 | Number functions: TO_CHAR, ROUND, TRUNC |

Objective | Interpret the effect of the number functions TO_CHAR, ROUND, and 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.

**ROUND Function:**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*series of images*show a query that lists a numbers with variations on the`ROUND`

function and the`TRUNC`

function.

**ROUND Function:**The purpose of the ROUND function is to round a given number or date to a specified decimal place or unit. In the case of numerical values, the ROUND function rounds the input value to the nearest whole number, or to a specified number of decimal places. For date values, it rounds to the nearest day, month, or year based on the input unit.

Syntax for ROUND function with numbers:

ROUND(number, decimal_places)

Syntax for ROUND function with dates:

ROUND(date, unit)

**TRUNC Function:**The TRUNC function, on the other hand, is used to truncate a given number or date to a specified decimal place or unit. For numerical values, the TRUNC function removes the fractional part of the number, effectively rounding down to the nearest whole number, or to a specified number of decimal places. For date values, it truncates to the nearest day, month, or year based on the input unit, effectively discarding the smaller units. Syntax for TRUNC function with numbers:

TRUNC(number, decimal_places)

Syntax for TRUNC function with dates:

TRUNC(date, unit)

In summary, the ROUND and TRUNC functions in Oracle serve the purpose of rounding and truncating numerical and date values, respectively. These functions are crucial in data processing, analysis, and reporting, allowing users to handle large sets of data with greater precision and control.

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function with syntax and examples.

Description: The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

Syntax (with numbers) : The syntax for the TRUNC function in Oracle/PLSQL is:

Parameters or Arguments : number

The number to truncate.decimal_placesOptional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.

Description: The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

Syntax (with numbers) : The syntax for the TRUNC function in Oracle/PLSQL is:

TRUNC( number [, decimal_places] )

Parameters or Arguments : number

The number to truncate.decimal_placesOptional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.

The TRUNC function can be used in the following versions of Oracle/PLSQL:

(*)Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

**Example:** (with numbers).
Let us look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.

For example:

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

(*)Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For example:

TRUNC(124.815) Result: 124 TRUNC(124.815, 0) Result: 124 TRUNC(124.815, 1) Result: 124.8 TRUNC(124.815, 2) Result: 124.81 TRUNC(124.815, 3) Result: 124.815 TRUNC(-124.815, 2) Result: -124.81 TRUNC(124.815, -1) Result: 120 TRUNC(124.815, -2) Result: 100 TRUNC(124.815, -3) Result: 0

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