singlerowfunctions
singlerowfunctions
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
15 rows selected.
SQL> connect
Enter user-name: hr
Enter password:
Connected.
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
UPPER(REGION_NAME)
-------------------------
EUROPE
AMERICAS
ASIA
MIDDLE EAST AND AFRICA
UPP
---
SQL
8 rows selected.
D
-
X
UPPE
----
JAVA
LOWER
-----
water
INITCAP(REGION_NAME)
-------------------------
Europe
Americas
Asia
Middle East And Africa
INITC
-----
Enemy
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
15 rows selected.
CONCAT(JOB,HIREDAT
------------------
actor
CLERK17-DEC-80
SALESMAN20-FEB-81
SALESMAN22-FEB-81
MANAGER02-APR-81
SALESMAN28-SEP-81
MANAGER01-MAY-81
MANAGER09-JUN-81
ANALYST19-APR-87
PRESIDENT17-NOV-81
SALESMAN08-SEP-81
CLERK23-MAY-87
CLERK03-DEC-81
ANALYST03-DEC-81
CLERK23-JAN-82
15 rows selected.
CONCAT(ENAME,
-------------
vijaysir
SUMANTHsir
ALLENsir
WARDsir
JONESsir
MARTINsir
BLAKEsir
CLARKsir
SCOTTsir
KINGsir
TURNERsir
ADAMSsir
JAMESsir
FORDsir
MILLERsir
15 rows selected.
CONCAT('MAHEN
-------------
mahendradhoni
CONCAT('MAHENDR
---------------
mahendra dhoni
CONCAT(UPPER(
-------------
MAHENDRADHONI
SUBS
----
ANIM
SUB
---
MAT
SUB
---
ONS
SUBS
----
ATIO
S
-
A
S
-
v
S
A
W
J
M
B
C
S
K
T
A
J
F
M
15 rows selected.
LENGTH('ANIMATIONS')
--------------------
10
LENGTH('ANIMALS')
-----------------
7
15 rows selected.
REPLACE('A
----------
$NIM$TIONS
REPLACE('A
----------
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
@NIM@TIONS
15 rows selected.
MOD(4,2)
----------
0
MOD(20,3)
----------
2
SYSDATE
---------
24-JUL-24
SYSDATE-1
---------
23-JUL-24
SYSDATE+1
---------
25-JUL-24
15 rows selected.
HIREDATE EXTRACT(YEARFROMHIREDATE)
--------- -------------------------
17-DEC-80 1980
20-FEB-81 1981
22-FEB-81 1981
02-APR-81 1981
28-SEP-81 1981
01-MAY-81 1981
09-JUN-81 1981
19-APR-87 1987
17-NOV-81 1981
08-SEP-81 1981
23-MAY-87 1987
03-DEC-81 1981
03-DEC-81 1981
23-JAN-82 1982
15 rows selected.
HIREDATE EXTRACT(DAYFROMHIREDATE)
--------- ------------------------
17-DEC-80 17
20-FEB-81 20
22-FEB-81 22
02-APR-81 2
28-SEP-81 28
01-MAY-81 1
09-JUN-81 9
19-APR-87 19
17-NOV-81 17
08-SEP-81 8
23-MAY-87 23
03-DEC-81 3
03-DEC-81 3
23-JAN-82 23
15 rows selected.
HIREDATE EXTRACT(MONTHFROMHIREDATE)
--------- --------------------------
17-DEC-80 12
20-FEB-81 2
22-FEB-81 2
02-APR-81 4
28-SEP-81 9
01-MAY-81 5
09-JUN-81 6
19-APR-87 4
17-NOV-81 11
08-SEP-81 9
23-MAY-87 5
03-DEC-81 12
03-DEC-81 12
23-JAN-82 1
15 rows selected.
15 rows selected.
SQL> DELETE FROM EMP WHERE ENAME='vijay';
1 row deleted.
14 rows selected.
14 rows selected.
14 rows selected.
TO_CHAR(SYSDATE,'YYYYYEARMMMONTHMONDYDAYDD')
---------------------------------------------------------------------------------
2024 twenty twenty-four 07 july jul wed wednesday 24
SQL>