from dual;
SUBSTR( SUBST
SUBSTR
---------- -------
--------
omputer omput mputer
1 If no_of_chars parameter is
negative then it will display nothing.
2 If both parameters except string
are null or zeros then it will display nothing.
3 If no_of_chars parameter is
greater than the length of the string then it ignores and calculates based on
the orginal string length.
4 If start_chr_count is negative
then it will extract the substring from right end.
1 2 3 4 5 6 7 8
C O M P U T E R
-8 -7 -6 -5 -4 -3 -2 -1
q) INSTR
This will allows you for searching
through a string for set of characters.
Syntax:
instr (string, search_str [, start_chr_count [, occurrence] ])
Ex:
SQL> select instr('information','o',4,1),
instr('information','o',4,2) from dual;
INSTR('INFORMATION','O',4,1) INSTR('INFORMATION','O',4,2)
------------------------------------ -------------------------------------
4 10
1
If you are not specifying start_chr_count and occurrence
then it will start
search from the
beginning and finds first occurrence only.
2
If both parameters start_chr_count and occurrence are
null, it will display
nothing.
r) DECODE
Decode will act as value by value
substitution.
For every value of field, it will checks
for a match in a series of if/then tests.
Syntax:
decode (value, if1, then1, if2, then2, ……. else);
Ex:
SQL> select sal, decode(sal,500,'Low',5000,'High','Medium') from
emp;
SAL DECODE
----- ---------
500 Low
2500
Medium
2000 Medium
3500 Medium
3000 Medium
5000 High
4000 Medium
5000 High
1800 Medium
1200 Medium
2000 Medium
2700 Medium
2200 Medium
3200 Medium
SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from dual;
DECODE(1,1,3) DECODE(1,2,3,4,4,6)
----------------- ------------------------
3 6
1
If the number of parameters are odd and different then decode will
display
nothing.
2
If the number of parameters are even and different then decode will
display last
value.
3
If all the parameters are null then decode will display nothing.
4
If all the parameters are zeros then decode will display zero.
s) GREATEST
This will give the greatest string.
Syntax:
greatest (strng1, string2, string3 … stringn)
Ex:
SQL> select greatest('a', 'b', 'c'),
greatest('satish','srinu','saketh') from dual;
GREAT GREAT
------- -------
c srinu
7
If all the parameters are nulls then it will display nothing.
8
If any of the parameters is null it will display nothing.
t) LEAST
This will give the least string.
Syntax:
greatest (strng1, string2, string3 … stringn)
Ex:
SQL> select least('a', 'b', 'c'),
least('satish','srinu','saketh') from dual;
LEAST LEAST
------- -------
a saketh
9
If all the parameters are nulls then it will display nothing.
10
If any of the parameters is null it will display nothing.
u) COALESCE
This will gives the first non-null string.
Syntax:
coalesce (strng1, string2, string3 … stringn)
Ex:
SQL> select coalesce('a','b','c'), coalesce(null,'a',null,'b') from
dual;
COALESCE COALESCE
-----------
-----------
a a
DATE
FUNCTIONS
39 Sysdate
40 Current_date
41 Current_timestamp
42 Systimestamp
43 Localtimestamp
44 Dbtimezone
45 Sessiontimezone
46 To_char
47 To_date
48 Add_months
49 Months_between
50 Next_day
51 Last_day
52 Extract
53 Greatest
54 Least
55 Round
56 Trunc
57 New_time
58 Coalesce
Oracle
default date format is DD-MON-YY.
We
can change the default format to our desired format by using the following
command.
SQL> alter session set nls_date_format
= ‘DD-MONTH-YYYY’;
But this will expire once the session
was closed.
a) SYSDATE
This will give the current date and time.
Ex:
SQL> select sysdate from dual;
SYSDATE
-----------
24-DEC-06
b) CURRENT_DATE
This will returns the current date in the
session’s timezone.
Ex:
SQL> select current_date from dual;
CURRENT_DATE
------------------
24-DEC-06
c) CURRENT_TIMESTAMP
This will returns the current timestamp
with the active time zone information.
Ex:
SQL> select current_timestamp from
dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.42.41.383369 AM +05:30
d) SYSTIMESTAMP
This will returns the system date,
including fractional seconds and time zone of the
database.
Ex:
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.49.31.830099 AM +05:30
e) LOCALTIMESTAMP
This will returns local timestamp in the
active time zone information, with no time
zone information shown.
Ex:
SQL>
select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.44.18.502874 AM
f) DBTIMEZONE
This will returns the current database time
zone in UTC format. (Coordinated Universal
Time)
Ex:
SQL>
select dbtimezone from dual;
DBTIMEZONE
---------------
-07:00
g) SESSIONTIMEZONE
This will returns the value of the current
session’s time zone.
Ex:
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
------------------------------------
+05:30
h) TO_CHAR
This will be used to extract various date
formats.
The available date formats as follows.
Syntax:
to_char (date, format)
DATE FORMATS
D -- No of days in week
DD -- No of days in month
DDD -- No of days in year
MM -- No of month
MON -- Three letter abbreviation of month
MONTH -- Fully spelled out month
RM --
Roman numeral month
No comments:
Post a Comment