Predefined Functions

FUNCTIONS

Functions can be categorized as follows.

  • Single row functions
  • Group functions

SINGLE ROW FUNCTIONS
Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row.

  • Numeric functions
  • String functions
  • Date functions
  • Miscellaneous functions
  • Conversion functions

NUMERIC FUNCTIONS

  • Abs
  • Sign
  • Sqrt
  • Mod
  • Nvl
  • Power
  • Exp
  • Ln
  • Log
  • Ceil
  • Floor
  • Round
  • Trunk
  • Bitand
  • Greatest
  • Least
  • Coalesce

a) ABS
Absolute value is the measure of the magnitude of value.
Absolute value is always a positive number.
Syntax: abs (value)
Ex:
SQL> select abs(5), abs(-5), abs(0), abs(null) from dual;
ABS(5) ABS(-5) ABS(0) ABS(NULL)
———- ———- ———- ————-
5 -5 0

b) SIGN
Sign gives the sign of a value.
Syntax: sign (value)
Ex:
SQL> select sign(5), sign(-5), sign(0), sign(null) from dual;
SIGN(5) SIGN(-5) SIGN(0) SIGN(NULL)
———- ———- ———- ————–
1 -1 0

c) SQRT
This will give the square root of the given value.
Syntax: sqrt (value) — here value must be positive.
Ex:
SQL> select sqrt(4), sqrt(0), sqrt(null), sqrt(1) from dual;
SQRT(4) SQRT(0) SQRT(NULL) SQRT(1)
———- ———- ————— ———-
2 0 1

d) MOD
This will give the remainder.
Syntax: mod (value, divisor)
Ex:
SQL> select mod(7,4), mod(1,5), mod(null,null), mod(0,0), mod(-7,4) from dual;
MOD(7,4) MOD(1,5) MOD(NULL,NULL) MOD(0,0) MOD(-7,4)
———— ———- ——————— ———– ————-
3 1 0 -3

e) NVL
This will substitutes the specified value in the place of null values.
Syntax: nvl (null_col, replacement_value)
Ex:
SQL> select * from student; — here for 3rd row marks value is null
NO NAME MARKS
— ——- ———
1 a 100
2 b 200
3 c

SQL> select no, name, nvl(marks,300) from student;
NO NAME NVL(MARKS,300)
— ——- ———————
1 a 100
2 b 200
3 c 300

SQL> select nvl(1,2), nvl(2,3), nvl(4,3), nvl(5,4) from dual;
NVL(1,2) NVL(2,3) NVL(4,3) NVL(5,4)
———- ———- ———- ———-
1 2 4 5

SQL> select nvl(0,0), nvl(1,1), nvl(null,null), nvl(4,4) from dual;
NVL(0,0) NVL(1,1) NVL(null,null) NVL(4,4)
———- ———- —————– ———-
0 1 4

f) POWER
Power is the ability to raise a value to a given exponent.
Syntax: power (value, exponent)
Ex:
SQL> select power(2,5), power(0,0), power(1,1), power(null,null), power(2,-5) from dual;

POWER(2,5) POWER(0,0) POWER(1,1) POWER(NULL,NULL) POWER(2,-5)
————– ————– —– ——— ———————– —————
32 1 1 .03125

g) EXP
This will raise e value to the give power.
Syntax: exp (value)
Ex:
SQL> select exp(1), exp(2), exp(0), exp(null), exp(-2) from dual;
EXP(1) EXP(2) EXP(0) EXP(NULL) EXP(-2)
——– ——— ——– ————- ———-
2.71828183 7.3890561 1 .135335283

h) LN
This is based on natural or base logarithm.
Syntax: ln (value) — here value must be greater than zero which is positive only.
Ex:
SQL> select ln(1), ln(2), ln(null) from dual;
LN(1) LN(2) LN(NULL)
——- ——- ————
0 .693147181
Ln and Exp are reciprocal to each other.
EXP (3) = 20.0855369
LN (20.0855369) = 3

i) LOG
This is based on 10 based logarithm.
Syntax: log (10, value) — here value must be greater than zero which is positive only.
Ex:
SQL> select log(10,100), log(10,2), log(10,1), log(10,null) from dual;
LOG(10,100) LOG(10,2) LOG(10,1) LOG(10,NULL)
————— ———– ———— —————–
2 .301029996 0
LN (value) = LOG (EXP(1), value)

SQL> select ln(3), log(exp(1),3) from dual;
LN(3) LOG(EXP(1),3)
——- —————–
1.09861229 1.09861229

j) CEIL
This will produce a whole number that is greater than or equal to the specified value.
Syntax: ceil (value)
Ex:
SQL> select ceil(5), ceil(5.1), ceil(-5), ceil( -5.1), ceil(0), ceil(null) from dual;
CEIL(5) CEIL(5.1) CEIL(-5) CEIL(-5.1) CEIL(0) CEIL(NULL)
——— ———– ———- ———— ——– ————–
5 6 -5 -5 0

k) FLOOR
This will produce a whole number that is less than or equal to the specified value.
Syntax: floor (value)
Ex:
SQL> select floor(5), floor(5.1), floor(-5), floor( -5.1), floor(0), floor(null) from dual;
FLOOR(5) FLOOR(5.1) FLOOR(-5) FLOOR(-5.1) FLOOR(0) FLOOR(NULL)
———– ————- ———— ————– ———– —————-
5 5 -5 -6 0

l) ROUND
This will rounds numbers to a given number of digits of precision.
Syntax: round (value, precision)
Ex:
SQL> select round(123.2345), round(123.2345,2), round(123.2354,2) from dual;
ROUND(123.2345) ROUND(123.2345,0) ROUND(123.2345,2) ROUND(123.2354,2)
——————— ———————— ———————– ———————–
123 123 123.23 123.24
SQL> select round(123.2345,-1), round(123.2345,-2), round(123.2345,-3),
round(123.2345,-4) from dual;
ROUND(123.2345,-1) ROUND(123.2345,-2) ROUND(123.2345,-3) ROUND(123.2345,-4)
———————— ————————- ———————— ——————-
120 100 0 0

SQL> select round(123,0), round(123,1), round(123,2) from dual;
ROUND(123,0) ROUND(123,1) ROUND(123,2)
—————– —————– —————-
123 123 123

SQL> select round(-123,0), round(-123,1), round(-123,2) from dual;
ROUND(-123,0) ROUND(-123,1) ROUND(-123,2)
—————— —————– ——————-
-123 -123 -123

SQL> select round(123,-1), round(123,-2), round(123,-3), round(-123,-1), round(-123,-2), round(-123,-3) from dual;
ROUND(123,-1) ROUND(123,-2) ROUND(123,-3) ROUND(-123,-1) ROUND(-123,-2) ROUND(-123,-3)
————- ————- ————- ————– ————– ————————–
120 100 0 -120 -100 0

SQL> select round(null,null), round(0,0), round(1,1), round(-1,-1), round(-2,-2) from dual;
ROUND(NULL,NULL) ROUND(0,0) ROUND(1,1) ROUND(-1,-1) ROUND(-2,-2)
———————– ————– ————– —————- —————-
0 1 0 0

m) TRUNC
This will truncates or chops off digits of precision from a number.
Syntax: trunc (value, precision)
Ex:
SQL> select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;
TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
——————— ———————– ———————–
123 123.23 123.23
SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),
trunc(123.2345,-4) from dual;
TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4)
———————— ———————— ———————– ————————
120 100 0 0
SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual;
TRUNC(123,0) TRUNC(123,1) TRUNC(123,2)
—————- —————- —————–
123 123 123
SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;
TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2)
—————– —————– —————–
-123 -123 -123
SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2), trunc(-123,-3) from dual;
TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2)TRUNC(-123,-3)
————- ————- ————- ————– ————- ———————————
120 100 0 -120 -123 0

SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;
TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2)
———————– ————- ————- ————— —————-
0                     1              0              0

n) BITAND
This will perform bitwise and operation.
Syntax: bitand (value1, value2)
Ex:
SQL> select bitand(2,3), bitand(0,0), bitand(1,1), bitand(null,null), bitand(-2,-3)
from dual;
BITAND(2,3) BITAND(0,0) BITAND(1,1) BITAND(NULL,NULL) BITAND(-2,-3)
————– ————— ————– ———————— —————–
2                  0                  1                                                 -4

o) GREATEST
This will give the greatest number.
Syntax: greatest (value1, value2, value3 … valuen)
Ex:
SQL> select greatest(1, 2, 3), greatest(-1, -2, -3) from dual;
GREATEST(1,2,3)  GREATEST(-1,-2,-3)
——————–      ———————–
3                                  -1
If all the values are zeros then it will display zero.
If all the parameters are nulls then it will display nothing.
If any of the parameters is null it will display nothing.

p) LEAST
This will give the least number.
Syntax: least (value1, value2, value3 … valuen)
Ex:
SQL> select least(1, 2, 3), least(-1, -2, -3) from dual;
LEAST(1,2,3) LEAST(-1,-2,-3)
——————– ———————–
1 -3
1. If all the values are zeros then it will display zero.
2. If all the parameters are nulls then it will display nothing.
3. If any of the parameters is null it will display nothing.

q) COALESCE
This will return first non-null value.
Syntax: coalesce (value1, value2, value3 … valuen)
Ex:
SQL> select coalesce(1,2,3), coalesce(null,2,null,5) from dual;
COALESCE(1,2,3) COALESCE(NULL,2,NULL,5)
——————-       ——————————-
1                             2

Related posts:

Leave a Reply