The following is a mini-workout of the basic Algebraic functions found in Oracle 11g. It includes ABS, CEILING, FLOOR, MOD, REMAINDER, NANVL, POWER, ROUND, SIGN, TRUNC and BUCKET_WIDTH. More functions can be found here.

ABS (Absolute Value)

This function returns the number of positions a number is from zero.

select abs(-10) from dual;

 ABS(-10)
----------
 10
1 row selected.

CEIL (Ceiling)

This function always rounds a number up to the next integer.

select ceil(1.2) "ceil" from dual;

 ceil
----------
 2
1 row selected.

select ceil(1.5) "ceil" from dual;

 ceil
----------
 2
1 row selected.

FLOOR

This function always rounds a number down to the next integer.

select floor(1.2) "floor" from dual;

 floor
----------
 1
1 row selected.

select floor(1.5) "floor" from dual;

 floor
----------
 1
1 row selected.

MOD (Modulo)  and Remainder

MOD

The modulo is the remainder after dividing two numbers. By default, it returns an integer.

select mod(10,3) "mod" from dual;

 mod
----------
 1
1 row selected.

This is one of my favorite functions because you can use it to operate on even or odd numbered data. The following for loop only prints the index where when it is divided by two, there is no remainder.

begin
 for i in 1..10 loop
 if mod(i,2)=0 then
 dbms_output.put_line (i);
 end if;
 end loop;
end;

2
4
6
8
10

NOTE: The actual formula for MOD is the following. It uses the FLOOR function as part of the solution.

m - n * FLOOR(m/n)

m = 14. n = 3.

14/3 = 4.67.  
FLOOR(4.67) = 4. 
4 * 3 = 12.
14 - 12 = 2.
MOD(14,3) = 2.

REMAINDER

This function is similar to MOD except that it uses the ROUND function whereas MOD uses FLOOR. I wouldn’t use this function for finding even numbers because of how it is rounded. The numbers that are returned from REMAINDER work as advertised but do not simply return the remainder as you may imagine it.

m - n * ROUND(m/n)

m = 14. n = 3.

14/3 = 4.67.  
ROUND(4.67) = 5. 
5 * 3 = 15.
14 - 15 = -1.
REMAINDER(14,3) = -1.

NANVL

This function only works for binary data. It’s a switch statement that will detect a ‘NaN’ value and replace it with the value specified.

nanvl (binary_data, displayed_if_nan)

select bin_double from float_point_demo;

BIN_DOUBLE
----------
 1234.56
 NAN

select nanvl(bin_double,0) "nanvl" from float_point_demo;

 nanvl
----------
 1234.56
 0

2 rows selected.

POWER

Power raises the first number by a power equal to the second number.

power (base, power)

select power(3,3) "power" from dual;

 power
----------
 27
1 row selected.

ROUND

The ROUND function returns a number rounded to the number of places specified. If the number of places is not specified, the number is rounded to an integer.

ROUND(number_to_round,[number_of_places])

select round(4.67) from dual;

ROUND(4.67)
-----------
 5
1 row selected.

select round(4.67,1) from dual;

ROUND(4.67,1)
-------------
 4.7
1 row selected.

select round(4.67,2) from dual;

ROUND(4.67,2)
-------------
 4.67
1 row selected.

SIGN

The SIGN function returns a -1 for negative numbers, 0 for 0 and 1 for positive numbers.

select sign(-5) from dual;

 SIGN(-5)
----------
 -1
1 row selected.

select sign(0) from dual;

 SIGN(0)
----------
 0
1 row selected.

select sign(5) from dual;

 SIGN(5)
----------
 1
1 row selected.

TRUNC

Trunc doesn’t round. It chops. It will chop a number to the number of places you specify. If it is not specify, it will truncate to an integer.

TRUNC(number_to_truncate, number_of_places)

select trunc(4.67) from dual;

TRUNC(4.67)
-----------
 4
1 row selected.

select trunc(4.67,1) from dual;

TRUNC(4.67,1)
-------------
 4.6
1 row selected.

select trunc(4.67,2) from dual;

TRUNC(4.67,2)
-------------
 4.67
1 row selected.

WIDTH_BUCKET

WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size. See the link for more information on this. Basically, it creates groupings over a range of values.

width_bucket(field, min_value, max_value, number_of_buckets)

select location_id, city, state, zip_code, width_bucket(location_id, 1,5,2) "bucket" from tbl_locations;

When needed, Oracle Database creates an underflow bucket numbered 0 and an overflow bucket numbered num_buckets+1. These buckets handle values less than min_valueand more than max_value and are helpful in checking the reasonableness of endpoints. In the above example, the overflow bucket had to be created because the maximum value was exceeded.