SQL :- Numeric Function

--====================================================

SQL :- Numeric Function

--====================================================

-- ABS(): It returns the absolute value of a number.

select ABS( -32 ), ABS( 32 ), ABS( -23.201 ) , ABS( 23.201 )  from dual

Result:-

ABS(-32)	ABS(32)	ABS(-23.201)	ABS(23.201)
32	32	23.201	23.201

------------------------------------------------------------------------------------------------

--MOD(): It returns the remainder of n divided by m.

SELECT MOD(18, 4),  MOD(9, 2) FROM DUAL

Result:-

MOD(18,4)	MOD(9,2)
2	1

------------------------------------------------------------------------------------------------

--POWER(): It returns m raised to the nth power.

SELECT POWER(4, 2), 4*4 FROM DUAL

Result:-

POWER(4,2)	4*4
16	16

------------------------------------------------------------------------------------------------

--SQRT(): It returns the square root of a number.

SELECT SQRT(4) , SQRT(9) , SQRT(16) , SQRT(25) FROM DUAL

Result:-

SQRT(4)	SQRT(9)	SQRT(16)	SQRT(25)
2	3	4	5

------------------------------------------------------------------------------------------------

--FLOOR(): It returns the largest integer value that is less than or equal to a number.

SELECT 25 , FLOOR(25.75), 26  FROM DUAL

Result:-

25	FLOOR(25.75)	26
25	25	26

------------------------------------------------------------------------------------------------

--CEIL    Returns the smallest whole number greater than or equal to a specified number.

SELECT 25 , CEIL(25.75), 26  FROM DUAL

Result:-

25	FLOOR(25.75)	26
25	26	26

------------------------------------------------------------------------------------------------

--GREATEST(): It returns the greatest value in a list of expressions.

SELECT GREATEST(30, 2, 36, 81, 125, 999) FROM DUAL

Result:- 999

------------------------------------------------------------------------------------------------

--LEAST(): It returns the smallest value in a list of expressions.

SELECT LEAST(30, 2, 36, 81, 125) FROM DUAL

Result:- 2

------------------------------------------------------------------------------------------------

--ROUND(): It returns a number rounded to a certain number of decimal places.

SELECT ROUND(5.553) FROM DUAL

Result:- 6

SELECT ROUND(5.553,2) FROM DUAL

Result:- 5.55

SELECT ROUND(5.553,1) FROM DUAL

Result:- 5.6

------------------------------------------------------------------------------------------------

--TRUNC    Truncates a number to a specified number of decimal places.

SELECT TRUNC(5.553) FROM DUAL

Result:- 5

SELECT TRUNC(5.553,2) FROM DUAL

Result:- 5.55

SELECT TRUNC(5.553,1) FROM DUAL

Result:- 5.5

------------------------------------------------------------------------------------------------

--SIGN(): It returns a value indicating the sign of a number.

SELECT SIGN(255.5), SIGN(-255.5) , DECODE (SIGN(:A) , -1, 'UP', 'DL'  ) STATE  FROM DUAL

Result:-

SIGN(255.5)	SIGN(-255.5)	STATE
1	-1	DL