NUMERIC FUNCTIONS
| NUMERIC FUNCTIONS | ||||
| Abs | Sign | Sqrt | Mod | Nvl |
| Power | Exp | Ln | Log | Ceil |
| Floor | Round | Trunk | Bitand | Greatest |
| Least | Coalesce | |||
| 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 |
| 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 |
| 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 |
| 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 |
| NVL
(This will substitute 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 |
|
| 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
|
| 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 |
| LN
(This is based on natural or base e 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 |
|
| 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 |
|
| 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 |
| 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
|
| 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 |
|
| 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) ———————— ———————— ———————– 120 100 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 |
|
| 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 |
| 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 |
| 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. |
|
| 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. |
|
| 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 |
Visit Today : 116
Visit Yesterday : 73
This Month : 1554
This Year : 28744
Comments