Oracle 11g : staging table

Question

I need Oracle 11g commands to create staging table.

table: Streets
Input field: Name 
output fields: Streets_Prefix
               Streets_Name
               Streets_Suffix

End users from a front end application fill information for only "Name" fields of "Streets" table as :

"AVE Mandela road South".

But in same table "Streets" other fields need to get parsed data from "Name" fields as :

Streets_Prefix : AVE

Streets_Name : Mandela road

Streets_Suffix : South

So here input and target table is same "Streets" but input and target fields are different so I need command to create staging table in which I can do Parsing of 'Name' field and update "Streets_Prefix", "Streets_Name" and "Streets_Suffix".

Answer 1

You can use the below queries to get the Streets_Prefix, Streets_Name,Streets_Suffix populated from Name column of STREETS table

select 
     substr(name, 0, instr(name,' ',1)-1) 
     as Streets_Prefix 
from STREETS  ;


select 
     substr(name, instr(name, ' ')+1,instr(name, ' ', -1, 1) - instr(name, ' ') - 1)
      as Streets_Name 
from STREETS  ;


select 
     substr(name, instr(name,' ',-1)+1) 
     as Streets_suffix
from STREETS  ;

OUTPUT:

STREETS_PREFIX

AVE
star
ZEBRA

STREETS_NAME

Mandela road
Bangalore road
CROSSING road

STREETS_SUFFIX

South
East
NORTH

create table STREETS  ( name varchar2(200)); --Considering you want only one column

For further reference : CREATE TABLE