SQL String Handling Functions using Oracle
Create an employee table
SQL> create table employee (id number, name varchar2(20),designation varchar2(20),salary number, dep_no number);
Insert a few records
SQL> insert into employee values (&id,'&name','&designation',&salary,&dep_no);
Enter value for id: 111
Enter value for name: satish verma
Enter value for designation: Associate
Enter value for salary: 10000
Enter value for dep_no: 421
old 1: insert into employee values (&id,'&name','&designation',&salary,&dep_no)
new 1: insert into employee values (111,'satish verma','Associate',10000,421)
Viewing the table
SQL> select * from employee;
Output
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
111 satish verma Associate 10000 421
112 ram Associate 20000 421
113 tom Assistant 5000 422
114 jeff Assistant 2000 422
115 mohan professor 50000 422
116 venkat professor 70000 422
6 rows selected.
View name column in uppercase
SQL> select upper(name) from employee;
Output
UPPER(NAME)
--------------------
SATISH VERMA
RAM
TOM
JEFF
MOHAN
VENKAT
6 rows selected.
using initcap to get the first letter of a word in capital letters
SQL> select initcap(name) from employee;
Output
INITCAP(NAME)
--------------------
Satish Verma
Ram
Tom
Jeff
Mohan
Venkat
6 rows selected.
Viewing length of the fields in the name column
SQL> select length(name) from employee;
Output
LENGTH(NAME)
------------
12
3
3
4
5
6
6 rows selected.
Performing Substring Operation
SQL> select substr(name,1,3) from employee where id=111;
Output
SUB
---
sat
Performing Concatenation Operation
SQL> select concat(name,designation) from employee;
Output
CONCAT(NAME,DESIGNATION)
----------------------------------------
satish vermaAssociate
ramAssociate
tomAssistant
jeffAssistant
mohanprofessor
venkatprofessor
6 rows selected.
Replacing a string with another using Select query
SQL> select replace(name,'verma','sharma') from employee where id=111;
Output
REPLACE(NAME,'VERMA','SHARMA')
--------------------------------------------------------------------------------
satish sharma
Replacing a string with another string using Update
SQL> update employee set name= replace(name,'verma','sharma') where id=111;
1 row updated.
SQL> select * from employee;
Output
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
111 satish sharma Associate 10000 421
112 ram Associate 20000 421
113 tom Assistant 5000 422
114 jeff Assistant 2000 422
115 mohan professor 50000 422
116 venkat professor 70000 422
6 rows selected.