Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function

SQL ABS, SIGN Function


SQL ABS Function

SQL ABS function return absolute value of n.

SQL ABS function take any numeric data type/non numeric data type value that are explicitly convert to numeric data type value and finally value return same data type at the time of function taken argument.

Supported Oracle SQL Version :

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Example :

following example return absolute value of the -15 value,

SQL> SELECT ABS(-15) FROM DUAL;

  ABS(-15)
----------
        15


SQL SIGN Function

SQL SIGN function return the sign of the n number.

If n value is a NUMBER data type and if:

  • n > 0 return value 1
  • n = 0 return value 0
  • n < 0 return value -1

If n value is a BINARY_FLOAT and BINARY_DOUBLE data type and if:

  • n >= 0 return value +1
  • n = NaN return value +1
  • n < 0 return value -1

Supported Oracle SQL Version :

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Example :

Consider following example take n number (10), return the sign of the number,

SQL> SELECT SIGN(-10) "SIGN" FROM DUAL;

      SIGN
----------
        -1

SQL> SELECT SIGN(10) "SIGN" FROM DUAL;

      SIGN
----------
         1

SQL> SELECT SIGN(0) "SIGN" FROM DUAL;

      SIGN
----------
         0