Oracle Tips by Burleson

Number Functions

Number functions allow you to present a number in a manner that is useful to the reader.  You must always be careful with converting numbers because you are only converting the presentation.  The number is in the database is still the same. 

round (n,d)

The round  function rounds a number n to the specified decimal d.  The decimal d can be positive, negative or zero.  If the decimal is positive, then the number is rounded to that many decimal points.  The number five rounds up.  If d is zero, then the number is rounded to no decimal points.  If d is negative, then the number will have no decimal points and it will be rounded to the d digits to the left of the decimal point.

SELECT
  ROUND( 1234.345, 2),
  ROUND( 1234.345, 0),
  ROUND( 1234.345, -2)
FROM
  dual;

ROUND(1234.345,2) ROUND(1234.345,0) ROUND(1234.345,-2)
----------------- ----------------- ------------------
          1234.35              1234               1200 

1 row selected. 

The boss now wants to know the retail price of the books we publish, rounded to the nearest dollar.  This is found in the book table. 

SELECT
  RPAD(TRIM(INITCAP(book_title)),40,'.') Title,
  ROUND(book_retail_price,0) Bucks
FROM
  book;

TITLE                                         BUCKS
---------------------------------------- ----------
Windows Success.........................         35
Piano Greats............................         33
Dos For Dummies.........................         20
The Zen Of Auto Repair..................        100
Zero Loss Finance.......................         22
Operations Research Theory..............         45
Non Violins In The Workplace............         12
Unix For Experts........................         39
Pay No Taxes And Go To Jail.............         11
The Fall Of Microsoft...................         20
Writers Market..........................         23
Managing Stress.........................         40
Bears Are People Too....................         35
Reduce Spending The Republican Way......         28
The Willow Weeps No More................         30
Oracle9i Sql Tuning.....................         50
Was George Washington Feeble?...........         25
Cooking Light...........................         25
Never Eat Meat..........................         11
How To Housebreak Your Horse............         30

20 rows selected.

trunc (n,d)

The trunc  or truncate function simply drops the digits without rounding.  The decimal d can again be positive, negative or zero.  If the number truncated is five or higher, it is still dropped without rounding the next digit up.

SELECT
  TRUNC( 1234.345, 2),
  TRUNC( 1234.345, 0),
  TRUNC( 1234.345, -2)
FROM
  dual;  

TRUNC(1234.345,2) TRUNC(1234.345,0) TRUNC(1234.345,-2)
----------------- ----------------- ------------------
          1234.34              1234               1200

1 row selected.

Notice the difference between trunc and round.

TRUNC(1234.345,2) TRUNC(1234.345,0) TRUNC(1234.345,-2)
----------------- ----------------- ------------------
          1234.34              1234               1200

ROUND(1234.345,2) ROUND(1234.345,0) ROUND(1234.345,-2)
---------------- ----------------- ------------------          1234.35              1234               1200

Like the character functions, there are more numeric functions that are mostly used by programmers such as sin, cos, tan, ceil, floor, etc. 

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm