Oracle 11g - parsing words of a column in a sql query -

i'm trying update city field. canada addresses, i'm going keep simple issue. city fields contain 1 word state field contains multiple words (usually no more 3 words separated space.), majority of state values contain 2 words separated space.

example: city show new , state show york ny

now trying update city show new york right now. have not gotten part need update state well. baby steps guess.

what have:

update table set city = city || ' ' || substr(state, 1, instr(state, ' ')) substr(state, 1, instr(state, ' ')) not null; 

when run :

select city || ' ' || substr(state, 1, instr(state, ' ')) substr(state, 1, instr(state, ' ')) not null;  

first word of state field, shows me i'm kind of on track.

any info on appreciated.

thanks.

if trying correct data (though , not 100% - if case) code seems fine.

create table t1 (city varchar2(100), state varchar2(100)) insert t1 values ('new','york ny')

update t1 set city = city ||' '||substr(state, 1, instr(state, ' ')) substr(state, 1, instr(state, ' ')) not null;

now city column : new york

select state ,substr(state, length(state) -1, instr(state, ' ')) t1
substr(state, 1, instr(state, ' ')) not null;