Define array in Oracle
i have coulumb stn varchar2(100) , i want to split it to 4 columbs n1 varcha2(20)...n4 varchar2(20) ,depend on space eg: stn : John Adms Smith Zac
n1 : John
n2 : Adms
n3 : Smith
n4 : Zac
i use oracle8.0.5 developer6
Software/Hardware used:
Software/Hardware used:
ASKED:
January 29, 2009 7:42 AM
UPDATED:
January 29, 2009 1:58 PM
Answer Wiki:
The INSTR function should help here.
If you know that it will allways be 4 parts in stn, you could use a query like this:
<pre>select substr(stn,1,instr(stn,' ',1,1)-1) n1,
substr(stn,instr(stn,' ',1,1)+1,instr(stn,' ',1,2)-1-instr(stn,' ',1,1)) n2,
substr(stn,instr(stn,' ',1,2)+1,instr(stn,' ',1,3)-1-instr(stn,' ',1,2)) n3,
substr(stn,instr(stn,' ',1,3)+1,length(stn)) n4
from YourTable;</pre>
If not, you could create a stored function or procedure with a similar logic.
Please reply if you need more help.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
