In SQL,We have different types of functions,lets learn those now.
 |
| 1.1 SQL Functions
In This post,lets learn about Numeric Functions:
NUMERIC FUNCTIONS:-
a) Abs b) Sign c) Sqrt d) Mod e) Nvl f) Power g) Exp h) Ln i) Log j) Ceil k) Floor l) Round m) Trunk n) Bitand o) Greatest p) Least q) Coalesce
In This diagram,i ill show only important numeric functions which are only useful for our real time scenarios.
 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 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 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
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. 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 |
No comments:
Post a Comment