SQL Tutorial – Day – 05

–Single Row functions in Oracle:
=================================

select first_name,upper(first_name),lower(first_name),initcap(first_name),
length(first_name),reverse(first_name) from employees;

select * from employees where first_name=’John’;

select * from employees where first_name=’john’;

select * from employees where lower(first_name)=’john’;

select * from employees where UPPER(first_name)=’JOHN’;

 

select address,lower(address) from customer_details;

select address from customer_details where lower(ADDRESS)=’chennai’;

select * from customer_details where address =’chennai’;

select * from customer_details where lower(address) =’chennai’;

select * from customer_details where upper(address) =’CHENNAI’;

——————————————————————————–
— Substr –sub string

substr(string,from_position,no_of_char); — 3 argument

substr(string,from_position); — 2 argument

select substr(‘Welcome to India!!!’,12,5) from dual;

select substr(‘Welcome to India!!!’,12) from dual;

select substr(‘Welcome to India!!!’,-8) from dual;

select substr(‘Welcome to India!!!’,-8,5) from dual;

select job_id,substr(job_id,1,4) from employees;

 

— Instr will return the position of the character

 

select INSTR(‘CORPORATE FLOOR’,’R’) from dual;

select INSTR(‘NIC IT ACADEMY’,’AB’) from dual;

— from 3rd position 2nd occurance
select INSTR(‘CORPORATE FLOOR’,’OR’,3,2) from dual;

select INSTR(‘CORPORATE FLOOR’,’OR’,3,1) from dual;

select substr(‘When system dialog prompts, click Open Zoom Meetings.’,1,
instr(‘When system dialog prompts, click Open Zoom Meetings.’,’,’)-1) from dual;

select substr(‘sample@gmail.com’, 1, instr(‘sample@gmail.com’, ‘@’)-1) from dual;

select substr(‘abc@gmail.com’, 1, instr(‘abc@gmail.com’, ‘@’)-1) from dual;

select INSTR(‘CORPORATE FLOOR GARDEN’,’ ‘) from dual;

select substr(‘CORPORATE FLOOR GARDEN’,17) from dual;

select instr(‘CORPORATE FLOOR GARDEN’,’ ‘,1,2) from dual;

select substr(‘CORPORATE FLOOR GARDEN’,instr(‘CORPORATE FLOOR GARDEN’,’ ‘,1,2)+1) from dual;

select substr(‘WELCOME TO CHENNAI CHROMPET’,instr(‘WELCOME TO CHENNAI CHROMPET’,’ ‘,1,3)+1) from dual;

select substr(‘asfdfadsfad@gmail.com’, 1, instr(‘asfdfadsfad@gmail.com’,’@’)-1) from dual;

 

 

select LPAD(‘WELCOME’,15,’*’) from dual;

select RPAD(‘WELCOME’,15,’*’) from dual;

select LPAD(RPAD(‘WELCOME’,15,’*’),30,’*’) from dual;

select salary,LPAD(salary,15,0) from employees;

——————————————————-

select substr(‘WELCOME TO ORACLE PLSQL’,instr(‘WELCOME TO ORACLE PLSQL’,’ ‘,1,3)+1) from dual;

instr(‘WELCOME TO ORACLE PLSQL’,’ ‘,1,3) — Returns the position of char from 1st to 3rd occurance so 18

18+1 — 19

substr(‘WELCOME TO ORACLE PLSQL’,19) — from 19th position to right hand side.

so PLSQL is the answer

——————————————————

Consider the below column value

Welcome to CHENNAI CHROMPET
WELCOME to BANGALURU ITPL
Welcome to MUMBAI KALYAN

Write one query to fetch only city value from the string

output should be:

CHENNAI
BANGALURU
MUMBAI

———————————————————————————-