Select initcap(Ename) From emp;
INITCAP(EN
----------
Smith
Allen
Ward
Jones
Martin
Blake
Clark
Scott
King
Turner
Adams
James
Ford
Miller
14 rows selected.
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 1 truncated.
1* Select Initcap('DURGA SOFT ') From Dua
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Initcap('DURGA SOFT ') From Dual
SQL> /
INITCAP('DU
-----------
Durga Soft
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
1* select concat('Oracle','Ds') from dual
SQL> /
CONCAT('
--------
OracleDs
SQL> ed
Wrote file afiedt.buf
1* select concat(concat('Oracle','Ds'),'Hyd') from dual
2 /
CONCAT(CONC
-----------
OracleDsHyd
SQL> ed
Wrote file afiedt.buf
1* select concat(concat('Oracle','Ds'),'Hyd') from dual
2
SQL>
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
1* Select Concat('&Fname',’&Sname’) “Full Name” From Dual;
2
SQL>
SQL> clear screen
SQL> Select Concat('&Fname','&Sname') "Full Name" From Dual;
Enter value for fname:
Enter value for sname:
old 1: Select Concat('&Fname','&Sname') "Full Name" From Dual
new 1: Select Concat('','') "Full Name" From Dual
F
-
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Concat('&Fname','&Sname') "Full Name" From Dual
2 /
Enter value for fname: Smith
Enter value for sname: Jan
old 1: Select Concat('&Fname','&Sname') "Full Name" From Dual
new 1: Select Concat('Smith','Jan') "Full Name" From Dual
Full Nam
--------
SmithJan
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',1,4) From Dual
2 /
SUBS
----
SIVA
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',1,4) From Dual
2
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',11) From Dual
2 /
SUBSTR(
-------
KRISHNA
SQL> clear screen
SQL> /
SUBSTR(
-------
KRISHNA
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 1 truncated.
1* Select Substr('SIVA RAMA KRISHNA',-7) From Dua
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',-7) From Dual
SQL> /
SUBSTR(
-------
KRISHNA
SQL> clear screen
SQL> /
SUBSTR(
-------
KRISHNA
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',-12,4) From Dual
2 /
SUBS
----
RAMA
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',-12,-4) From Dual
2 /
S
-
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',1.9,3.9) From Dual
SQL> /
SUB
---
SIV
SQL> ed
Wrote file afiedt.buf
1* Select Substr('SIVA RAMA KRISHNA',1.9,3.9) From Dual
2
SQL> /
SUB
---
SIV
SQL> clear screen
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 4 truncated.
1 Select Ename,Job
2 From Emp
3* Where Substr(Job,6)=Upper('man')
4 /
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
1 Select Concat(Initcap(Ename),
2 Concat(' is a ',Concat(Initcap(Substr(Job,1,3)),' Eater.')))
3 From Emp
4* Where Substr(Job,4,3)=Upper('Age')
5
SQL>
SQL> select substr('CLERK','4,3) from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ed
Wrote file afiedt.buf
1* select substr('CLERK',4,3) from dual
SQL> /
SU
--
RK
SQL> ed
Wrote file afiedt.buf
line 5 truncated.
1 Select Concat(Initcap(Ename),
2 Concat(' is a ',Concat(Initcap(Substr(Job,1,3)),' Eater.')))
3 From Emp
4* Where Substr(Job,4,3)=Upper('Age')
5 /
CONCAT(INITCAP(ENAME),CONC
--------------------------
Jones is a Man Eater.
Blake is a Man Eater.
Clark is a Man Eater.
SQL> clear screen
SQL> /
CONCAT(INITCAP(ENAME),CONC
--------------------------
Jones is a Man Eater.
Blake is a Man Eater.
Clark is a Man Eater.
SQL> ed
Wrote file afiedt.buf
1 Select Concat(Initcap(Ename),
2 Concat(' is a ',Concat(Initcap(Substr(Job,1,3)),' Eater.')))
3 From Emp
4* Where Substr(Job,4,3)=Upper('Age')
5 /
CONCAT(INITCAP(ENAME),CONC
--------------------------
Jones is a Man Eater.
Blake is a Man Eater.
Clark is a Man Eater.
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 1 truncated.
1* Select Length('E.F CODD') From Dua
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Length('E.F CODD') From Dual
SQL> /
LENGTH('E.FCODD')
-----------------
8
SQL> ed
Wrote file afiedt.buf
line 3 truncated.
1 Select Initcap(Ename),Job
2 From emp
3* Where Length(Ename)=
SQL>
SQL> ed
Wrote file afiedt.buf
1 Select Initcap(Ename),Job
2 From emp
3* Where Length(Ename)=8
SQL> /
no rows selected
SQL> ed
Wrote file afiedt.buf
1 Select Initcap(Ename),Job
2 From emp
3* Where Length(Ename)=5
SQL> /
INITCAP(EN JOB
---------- ---------
Smith CLERK
Allen SALESMAN
Jones MANAGER
Blake MANAGER
Clark MANAGER
Scott ANALYST
Adams CLERK
James CLERK
8 rows selected.
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 3 truncated.
1 Select Initcap(Ename),Job From Emp
2* Where Substr(Job,4,Length(Substr(Job,4,3)))='AGE'
3 /
INITCAP(EN JOB
---------- ---------
Jones MANAGER
Blake MANAGER
Clark MANAGER
SQL> clear screen
SQL> ed
Wrote file afiedt.buf
line 1 truncated.
1* Select Instr('SIVA RAMA KRISHNA','A',1,1) From Dua
SQL>
SQL>
SQL> ed
Wrote file afiedt.buf
1* Select Instr('SIVA RAMA KRISHNA','A',1,1) From Dual
SQL> /
INSTR('SIVARAMAKRISHNA','A',1,1)
--------------------------------
4
SQL> ed
Wrote file afiedt.buf
1* Select Instr('SIVA RAMA KRISHNA','A') From Dual
2 /
INSTR('SIVARAMAKRISHNA','A')
----------------------------
4
SQL> ed
Wrote file afiedt.buf
1* Select Instr('SIVA RAMA KRISHNA','A',10,2) From Dual
2 /
INSTR('SIVARAMAKRISHNA','A',10,2)
---------------------------------
0
SQL> ed
Wrote file afiedt.buf
1* Select Instr('SIVA RAMA KRISHNA','MA',7) From Dual
SQL> /
INSTR('SIVARAMAKRISHNA','MA',7)
-------------------------------
8
SQL> clear screen
SQL> clear screen
SQL> exit
