Numeric Functions

Contact Us or call 1-877-932-8228
Numeric Functions

Numeric Functions

Many mathematical operations and functions are available in Oracle (from simple arithmetic to advanced statistical functions). Numerical formatting is avaiable through the TO_CHAR method and rounding and trunctation to prescribed degrees of precision are supported as well.

Code Sample:

SQL-Functions-Non-Character/Demos/arithmetic.sql
--Arithmetic Operations (+,-,/,*) and MOD function 

SELECT 
	first_name, 
	last_name, 
	salary, 
	salary + 1000 AS salary_plus_1000
FROM employees 
WHERE last_name LIKE 'K%';

SELECT 
	first_name, 
	last_name, 
	salary, 
	salary - 1000 AS salary_minus_1000
FROM employees 
WHERE last_name LIKE 'K%';

SELECT 
	first_name, 
	last_name, 
	salary, 
	salary * 2 AS salary_doubled
FROM employees 
WHERE last_name LIKE 'K%';

SELECT 
	first_name, 
	last_name, 
	salary, 
	(salary / 6) AS salary_divided_by_six, 
	MOD(salary,6) AS the_remainder
FROM employees 
WHERE last_name LIKE 'K%';

Addition, Subtraction, Multiplication and Division are available using standard SQL operators (+,-,*,/). The MOD function can be used to calculate the modulous (or remainder) that results when two numbers are divided. Notice that the division operation results in some unwieldy results that are often rounded or truncated in practice.

Code Sample:

SQL-Functions-Non-Character/Demos/round.sql
SELECT 
	first_name, 
	last_name, 
	(salary / 6),
	ROUND(salary / 6), 
	ROUND(salary / 6, 2)
FROM employees 
WHERE last_name LIKE 'K%';

The ROUND function has several options available. A second parameter can be used to specify precision.

Code Sample:

SQL-Functions-Non-Character/Demos/trunc.sql
SELECT 
	first_name, 
	last_name, 
	(salary / 6),
	TRUNC(salary / 6), 
	TRUNC(salary / 6, 2)
FROM employees 
WHERE last_name LIKE 'K%';

The TRUNC function (like the ROUND function) allows for a second parameter to specify precision.

The TO_CHAR Function with numbers

There are variations of the TO_CHAR function that work with dates, numbers and other characters. The options available depend upon the data type being passed to the function.

There are a wide variety of format models that can be specified. The format model is a series of characters that describe the intended format for a number. This series of characters is interpreted by the TO_CHAR function and used to return a formatted string version of the number.

Some of the most common characters to use in format models include commas, decimal points, dollar signs, 0 (a numeric placeholder that includes leading zeroes) and 9 (a numberic placeholder that does not include leading zeroes).

Code Sample:

SQL-Functions-Non-Character/Demos/numeric_to_char.sql
SELECT 
	first_name, 
	last_name, 
	commission_pct,  
	TO_CHAR(commission_pct, '0.99'),
	TO_CHAR(commission_pct * 100,'99.00')
FROM employees 
WHERE last_name like 'K%';

This demonstrates two of the many format models available.

Next