Number Functions in SQL

Number Functions in SQL:

Number functions also called as NUMERIC functions. These functions are operate over number data and returns number/numeric data. Below are the following numeric functions available in SQL.

1. ABS(): 

It is used to converts the '-Ve'  value to '+Ve' value. It returns the absolute value of the number.
Syntax: ABS(numeric value)                           
Example : SELECT ABS(-20) FROM DUAL;
+----------------------------------------------------+
Output    :  20.                                                  

DUAL is a pre-defined virtual table which is having one row and one column. DUAL table is used to test pre-defined  and user-defined functions functionality.

SQL> SELECT * FROM DUAL;                      
+------------------------------------------------------+
Output:   X                                                          

2. MOD():

It returns/gives remainder after m divided by n.
Syntax: MOD(m,n)                                             
Example: SELECT MOD(11,2) FROM DUAL;
+------------------------------------------------------+
Output   :  1                                                         

3. GREATEST():

Greatest returns maximum value among the given/passed values.
Syntax: GREATEST(value1,value2...)                                    
Example: SELECT GREATEST(10,20,5,12) FROM DUAL:
+----------------------------------------------------------------------+
Output:   20.                                                                             

4. LOWEST():

Lowest returns the least value among the specified the values.
syntax: LOWEST(Value1,value2....)                                   
Example: SELECT LOWEST(10,2,55,22) FROM DUAL;
+-------------------------------------------------------------------+
Output:   2.                                                                            

5. ROUND():

It Rounds the given floated value number M based on the number N. If we are not passing value N then system rounds based on the value M.
Syntax: ROUND(m,n)                                                       
Example : SELECT ROUND(1.8) FROM DUAL;           
+-----------------------------------------------------------------+
Output    : 2                                                                        
Example : SELECT ROUND(1.23456,3) FROM DUAL;
+-----------------------------------------------------------------+
Output    : 1.235                                                                 

NOTE: Round always checks remaining(second parameter N) number . If remaining number is above 50% then automatically '1' added to the rounded number.

6. TRUNC():

It truncates the given floated value number 'm' based on the number 'n'.
Syntax    : TRUNC(m.n)                                                   
Example : SELECT TRUNC(1.7) FROM DUAL;           
+-----------------------------------------------------------------+
Output    : 1                                                                        
Example : SELECT TRUNC(1.23456,3) FROM DUAL;
+-----------------------------------------------------------------+
Output    : 1.234                                                                 

7. CEIL():

Ceil function is used to return the Nearest Greatest Integer and it always returns integer.
Syntax    : CEIL(value)                                                   
Example : SELECT CEIL(1.3) FROM DUAL;              
+---------------------------------------------------------------+
Output    : 2.                                                                     

8. FLOOR():

Floor function always returns the nearest lowest number.
Syntax    : FLOOR()                                                          
Example : SELECT FLOOR(1.8) FROM DUAL:            
+-----------------------------------------------------------------+
Output    : 1                                                                        

If any doubts, Please drop a comment below.

                                 Thank You.