Oracle Function Learning

Sql> Select substr (' Liuyuehui ', 3) as first_name from dual;

First_Name
--------------
Uyuehui

Sql> Select substr (' Liuyuehui ', 1) as first_name from dual;

First_Name
------------------
Liuyuehui

Sql> Select substr (' Liuyuehui ', 0) as first_name from dual;

First_Name
------------------
Liuyuehui

Sql> Select substr (' Liuyuehui ', -3,3) from dual;

SUBSTR
------
Hui

Sql> Select substr (' Liuyuehui ', Length (' Liuyuehui ')-5) as first_name from dual;

First_Name
------------
Yuehui

Sql> Select substr (' Feng ', lenth (' Feng ')-1) from dual;
Select substr (' Feng ', lenth (' Feng ')-1) from dual
*
An error occurred on line 1th:
ORA-00904: "Lenth": Invalid identifier

Sql> Select substr (' Feng ', Length (' Feng ')-1) from dual;

SUBS
----
Ng

Sql> Select substr (' Liufeng ', 3,7) as Firstname,substr (' Liufeng ', -4,4) as Secondn
Ame from dual;

FIRSTNAME Secondna
---------- --------
Ufeng Feng

Sql> Select substr (' Liufeng ', 0,3) as Firstname,substr (' Liufeng ', -4,4) as Secondn
Ame from dual;

FIRSTN Secondna
------ --------
Liu Feng

Sql> Select Upper (substr (' Liufeng ', 0,3)) as Firstname,lower (substr (' Liuyuehui ',
3) as secondname from dual;

FIRSTN Secondname
------ --------------
LIU Uyuehui

Sql> Select Upper (substr (' Liufeng ', 0,3)) as Firstname,lower (substr (' Liuyuehui ',
4) as secondname from dual;

FIRSTN Secondname
------ ------------
LIU Yuehui

Sql> Select InStr (' Hello world! ', ' or ') from dual;

INSTR (' helloworld! ', ' OR ')
-------------------------
8

Sql> Select trim (' Hello ') from dual;

TRIM (' HELL
----------
Hello

Sql> Select trim (' Hello ') as hi from dual;

Hi
----------
Hello

Sql> Select Lpad (' Smith ', Ten, ' * ') from dual;

Lpad (' SMITH ', 10, ' * ')
--------------------
Smith

Sql> Select Lpad (' Smith ', Ten, ' * ') as star_smith from dual;

Star_smith
--------------------
Smith

Sql> Select Rpad (' Smith ', Ten, ' * ') as star_smith from dual;

Star_smith
--------------------
smith*****

Sql> Select Rpad (' Smith ', Ten, ' * ') as Reft_star_smith,lpad (' Smith ', Ten, ' * ') as Righ
T_star_smith from dual;

Reft_star_smith Right_star_smith
-------------------- --------------------
smith***** *****smith

Sql> Select substr (InStr (' Testmyname ', ' or '), -4,4) from dual;

S
-

Sql>
Sql> Select substr (InStr (' Testmyname ', ' or '), -4,4) from dual;

S
-

Sql> Select InStr (' Testmyname ', ' or ') from dual;

INSTR (' Testmyname ', ' OR ')
------------------------
0

Sql> Select InStr (' Test myname ', ' or ') from dual;

INSTR (' Testmyname ', ' OR ')
------------------------
0

Sql> Select Rpad (' Smith ', 10, ' month ') as Reft_star_smith,lpad (' Smith ', Ten, ' * ') as rig
Ht_star_smith from dual;

Reft_star_smith Right_star_smith
------------------------ --------------------
Smith Month *****smith

Sql> Select Rpad (' Smith ', 10, ' month ') as Reft_star_smith,lpad (' Smith ', 10, ' month ') as Ri
Ght_star_smith from dual;

Reft_star_smith Right_star_smith
------------------------ ------------------------
Smith Month month month Smith

Sql> Select Next_day (sysdate, ' Monday ') from dual;

Next_day (SYSDA
--------------
1 September-January-15

Sql> Select Next_day (sysdate, ' Sunday ') from dual;
Select Next_day (sysdate, ' Sunday ') from dual
*
An error occurred on line 1th:
ORA-01846: Day of the week is invalid

Sql> Select Next_day (sysdate, ' Sunday ') from dual;

Next_day (SYSDA
--------------
1 August-January-15

Sql> Select Last_day (sysdate) from dual;

Last_day (SYSDA
--------------
3 January-January-15

Sql> Select Add_months (sysdate,1) from dual;

Add_months (SYS
--------------
1 April-February-15

Sql> Select Add_months (sysdate,1) as next_month from dual;

Next_month
--------------
1 April-February-15

Sql> Select To_char (sysdate, ' yyyy ') from dual;

To_char (
--------
2015

Sql> Select To_char (sysdate, ' mmmm ') from dual;

To_char (
--------
0101

Sql> Select To_char (sysdate, ' mm ') from dual;

To_c
----
01

Sql> Select To_char (sysdate, ' Mm-ss ') from dual;

To_char (SY
----------
01-52

Sql> Select To_char (sysdate, ' D ') from dual;//return week
2
Sql> Select To_char (sysdate, ' D ') from dual;
Select To_char (sysdate, ' D ') from dual
*
An error occurred on line 1th:
ORA-01756: string in quotation marks does not end correctly

Sql> Select To_char (sysdate, ' D ') from dual;

To
--
4

Sql> Select to_date (? 20090210?,? yyyyMMdd?) from dual;
Select to_date (? 20090210?,? yyyyMMdd?) from dual
*
An error occurred on line 1th:
ORA-00911: Invalid character

Sql> Select To_date (' 20090210 '?,? yyyyMMdd?) from dual;
ERROR:
ORA-01756: string in quotation marks does not end correctly

sql> Select to_date (' 20090210 '?,? yyyyMMdd?) from dual;
Select to_date (' 20090210 '?,? yyyyMMdd?) from dual
*
An error occurred on line 1th:
ORA-00911: Invalid character

sql> Select to_date (' 20090210 ', YyyyMMdd?) from dual;
Select to_date (' 20090210 ', YyyyMMdd?) from dual
*
An error occurred on line 1th:
ORA-00911: Invalid character

sql> Select to_date (' 20090210 ', ' yyyyMMdd '?) from dual;
Select to_date (' 20090210 ', ' yyyyMMdd '?) from dual
*
An error occurred on line 1th:
ORA-00911: Invalid character

sql> Select to_date (' 20090210 ', ' YYYYMMDD ') from dual;

To_date (' 20090
--------------
October-February-09

Sql> select Empno, ename, Sal,
2 Case Deptno
3 when ten then ' finance Department '
4 when the "Research and Development Department"
5 When the "Sales department"
6 Else ' unknown Department '
7 End Department
8 from EMP;
From EMP
*
An error occurred on line 8th:
ORA-00942: Table or view does not exist

Sql> select Empno, Lpad (Initcap (Trim (ename)), ten, ') name, job, Sal from EMP;
Select Empno, Lpad (Initcap (Trim (ename), ten, ") name, job, Sal from EMP
*
An error occurred on line 1th:
ORA-00942: Table or view does not exist

Sql> from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit produc
tion
With the partitioning, OLAP, Data Mining and Real application testing options break
Open

C:\users\yuehui>

C:\users\yuehui>

C:\users\yuehui>

Oracle Function Learning