Write a query to display Extract the code from these address

 Source:

ADDR

Hyderabad, HYD

Banglore, BANG

Mumbai, MUM

Delhi, DEL

Target:

ADDR	CODE
Hyderabad, HYD	HYD
Banglore, BANG	BANG
Mumbai, MUM	MUM
Delhi, DEL	DEL

drop table t_addr;

create table t_addr(addr varchar2(20));

insert into t_addr values('Hyderabad, HYD');

insert into t_addr values('Banglore, BANG');

insert into t_addr values('Mumbai, MUM');

insert into t_addr values('Delhi, DEL');

commit;

select * from t_addr;

select addr,instr(addr,','),

--,trim(

substr(addr,instr(addr,',')+1) cap_code

from t_addr;

select addr,instr(addr,','),substr(addr,instr(addr,',')+2)

code

from t_addr;

Step 1:

select addr,instr(addr,',') col from t_addr;

Output:

Hyderabad, HYD    10
Banglore, BANG    9
Mumbai, MUM       7
Delhi, DEL        6

Step 2:

select addr,instr(addr,',') col,substr(addr,instr(addr,','))

code

from t_addr;

Output:

Hyderabad, HYD    10    , HYD
Banglore, BANG     9    , BANG
Mumbai, MUM        7    , MUM
Delhi, DEL         6    , DEL

To remove spaces and , we are using +2

Step 3:

select addr,substr(addr,instr(addr,',')+2) code

from t_addr;

Output:

Hyderabad, HYD    HYD
Banglore, BANG    BANG
Mumbai, MUM       MUM
Delhi, DEL        DEL

or

To remove spaces we are using Trim Function

select addr,trim(substr(addr,instr(addr,',')+1)) code

from t_addr;

Output:

Hyderabad, HYD    HYD
Banglore, BANG    BANG
Mumbai, MUM       MUM
Delhi, DEL        DEL