Number Functions

Number Functions

ABS	SIGN	MOD	ROUND	TRUNC	FLOOR	CEIL	BETWEEN

---

ABS(Value)

• Returns Absolute value of inputted value

Examples

SELECT ABS(-666), ABS(6969), ABS(0) 
FROM Dual;

Output:

ABS(-666)	ABS(6969)	ABS(0)
666	6969	0

---

SIGN(value)

• Returns sign of given value. (-1,0,1)

Examples

Code:

SELECT SIGN(-69), SIGN(666), SIGN(0)
FROM DUAL;

Output:

SIGN(-69)	SIGN(666)	SIGN(0)
-1	1	0

---

MOD(value, divisor)

• Returns remainder

Examples

Simple Query for Modulus Calculation

SELECT MOD(8,3), MOD(8,4), MOD(8,5)
FROM Dual; 

MOD(8,3)	MOD(8,4)	MOD(8,5)
2	0	3
Query for all even numbers using MOD

SELECT * 
FROM Student.Student
WHERE MOD(Zip, 2) = 0;

First_Name	Last_Name	Zip
Josh	Kennedy	66666
Chad	Jensen	66224

---

Round(value [,precision])

• Will round given value to the provided precision

• Precision parameter relies on a given number.

  • If NULL, then SQL Developer assumes 0 precision and therefore will Round and then truncate all decimals

  • Negative numbers count left of the decimal

  • Positive numbers count right of the decimal

Examples

SELECT ROUND(638.51,0), ROUND(638.51), ROUND(638.51, -1),  ROUND(638.51, 1)
FROM Dual;

Output:

ROUND(638.51,0)	ROUND(638.51)	ROUND(638.51, -1)	ROUND(638.51, 1)
639	639	640	638.5

---

TRUNC(value [,precision])

• Truncates value
• Precision parameter relies on a given number.

  • If NULL, then SQL Developer assumes 0 precision and therefore will truncate all decimals.

  • Negative numbers count left of the decimal

  • Positive numbers count right of the decimal

Examples

SELECT TRUNC(638.51,0), TRUNC(638.51), TRUNC(638.51, -1), 
                        TRUNC(638.51, 1), TRUNC(638.51, -2)
FROM Dual;

Output:

TRUNC(638.51,0)	TRUNC(638.51)	TRUNC(638.51, -1)	TRUNC(638.51, 1)	TRUNC(638.51, -1)
638	638	630	638.5	600

---

FLOOR(value)

• Returns the largest integer less than or equal to the specified numeric expression

Examples

SELECT FLOOR(5.33), FLOOR(.99), FLOOR(1.00001)
FROM Dual;

Output:

FLOOR(5.33)	FLOOR(.99)	FLOOR(1.0001)
5	0	1

---

CEIL(value)

• Returns the smallest integer greater than or equal to the specified numeric expression.

Examples

SELECT CEIL(5.33), CEIL(.99), CEIL(1.00001)
FROM Dual

Output:

CEIL(5.33)	CEIL(.99)	CEIL(1.0001)
6	1	2

---

BETWEEN value and value2

• Returns a range of numbers between value1, and value 2
• NOT BETWEEN will return numbers outside this range

Examples

SELECT Section_ID
FROM Student.Section
Where Section_ID between 10 and 90;

SELECT Section_ID
FROM Student.Section
Where Section_ID NOT BETWEEN 10 and 90;

---