SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

to split a column into multiple columns

va44725815Mar 4 2008 — edited Mar 5 2008

Hi,
I have column and i want to split it into three columns as shown below given inputs and required outputs. I have written this query to get P_1, and p_2 but dont know how to get p_3.
SELECT ADDRESS,SUBSTR(ADDRESS,1, INSTR(ADDRESS,' ')-1) P_1
,SUBSTR(ADDRESS, instr(address,' ',-1)+1) P_2
FROM TT
Input:
ADDRESS
1 AVENUE OF THE AMERICAS
2 NORTH CAROLINE STREET EAST
236 TURPENTINE ROAD COOPER CREEK
REQUIRED OUTPUT
P_1 P_2 P_3
1 AMERICAS AVENUE OF THE
2 EAST NORTH CAROLINE STREET
236 CREEK TURPENTINE ROAD COOPER
Thanks in advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 2 2008

Added on Mar 4 2008

7 comments

4,340 views