ROUND, TRUNCATE and FLOOR function in SQL

Read this Oracle Tutorial Point article to know NUMBER function, ROUND function, TRUNCATE function, CEIL function, FLOOR function, MOD(MODULE) function, POWER function, SQRT(SQUARE ROOT) function, ABS(ABSOLUTE) function and SIGN functions in SQL.

NUMBER Function:
NUMBER functions accepts number as input and return number as values. Many functions return values that are accurate to 38 decimal digits.

ROUND Function:
In the below syntax, it returns ‘n’ rounded to n places right of the decimal point. If ‘m’ is omitted then n is rounded to 0 places. ‘m’ can be negative and rounds off the digits to the left of the decimal point. ‘m’ must be a integer.

Syntax:       ROUND(n,m)

Examples:
SQL> SELECT ROUND(18.195,1) FROM DUAL;

SQL> SELECT ROUND(18.195,-1) FROM DUAL;

SQL> SELECT ROUND(15648,2), ROUND(7854.565,0), ROUND(785.456,-1) FROM DUAL;

TRUNCATE Function:
In the below syntax, it returns n truncated to m decimal places. If ‘m’ is omitted, ‘n’ is truncated to 0 places. ‘n’ can be negative to truncate ‘m’ digits left to the decimal point.

Syntax:      TRUNC(n,m)

Examples:
SQL> SELECT TRUNC (18.195,1) FROM DUAL;

SQL> SELECT TRUNC (18.195,-1) FROM DUAL;

SQL> SELECT TRUNC (15648,2), TRUNC (7854.565,0), TRUNC (785.456,-1) FROM DUAL;

CEIL Function:
In the below syntax, it returns the smallest integer greater than or equals to ‘n’. the adjustment is done to the highest nearest decimal value.

Syntax:      CEIL(n)

Examples:
SQL> SELECT CEIL(19.7) FROM DUAL;

SQL> SELECT CEIL(15.37), CEIL(45.45), CEIL(15) FROM DUAL;

FLOOR Function:
In the below syntax, it returns the largest integer less than or equals than n. The adjustment is done to the lowest nearest decimal values.

Syntax:        FLOOR(n)

Examples:
SQL> SELECT FLOOR(19.7) FROM DUAL;

SQL> SELECT FLOOR(15.28), FLOOR(45,3) FROM DUAL;

MODULES Function:
In the below syntax, MOD function returns remainder m divided by n. It returns m if n is 0.

Syntax:      MOD(n,m)

Examples:
SQL> SELECT MOD(14,4), MOD(40,2) FROM DUAL;

POWER Function:
In the below syntax, it returns m raised to the n power. The base m and the exponent n can be any numbers. If m is negative then n must be an integer.

Syntax:    POWER(m,n)

Examples:
SQL> SELECT POWER(5,2), POWER(-5,2) FROM DUAL;

SQL> SELECT POWER(5,-2), POWER(-5,-2) FROM DUAL;

SQL> SELECT POWER(5,-2.5), POWER(-5,2.5) FROM DUAL;

SQUARE ROOT Function:
In the below syntax, it returns square root of n. The value of n cannot be negative. SQRT returns a real result. 

Syntax:     SQRT(n)

Examples:
SQL> SELECT SQRT(36) FROM DUAL;

ABSOLUTE Function:
In the below syntax, it returns the absolute value of n.

Syntax:     ABS(n)

Examples:
SQL> SELECT ABS(-19) FROM DUAL;

SQL> SELECT SAL,COMM,SAL-COMM, ABS(SAL-COMM) FROM EMP;

SIGN Function:
In the below syntax, it returns the SIGN(signification of a number).
  • If n<0, return -1
  • If n=0, return 0
  • If n>0, return 1
Syntax:     SIGN(n)

Examples:
SQL> SELECT SIGN(-15), SIGN(15), SIGN(0) FROM DUAL;

SQL> SELECT ENAME,SAL,COMM,SIGN(SAL-COMM) FROM EMP WHERE SIGN(SAL-COMM)=-1;

Comments