Define array in Oracle

10 pts.
Tags:
Arrays
Oracle 8i
Oracle development
SQL
VARCHAR
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following