ali -- Thanks for the question regarding "insert in table from a field", version sql

You Asked

dear sir,
please help me in this problem.
i want to insert into a table with many fields from a table with one field
ex..
table temp with one field --> aa1
aa1 contents is :
1;aaa;44455;23;

the other table with 4 fields --> aa2
aa2 fields is :
a1
a2
a3
a4

i can't write a code that insert from aa1 to aa2 like this:
a1 ----> 1
a2 ----> aaa
a3 ----> 44455
a4 ----> 23

note that i put in the first table ';' as a delimeter

please help me as soon as possible

and we said...

read about substr.

ops$tkyte%ORA10GR2> select aa1,
  2         substr(aa1,1,instr(aa1,';')-1) aa1_1,
  3         substr(aa1,instr(aa1,';')+1,instr(aa1,';',1,2)-instr(aa1,';')-1) aa1_2,
  4         substr(aa1,instr(aa1,';',1,2)+1,instr(aa1,';',1,3)-instr(aa1,';',1,2)-1) 
aa1_3,
  5         substr(aa1,instr(aa1,';',1,3)+1,instr(aa1,';',1,4)-instr(aa1,';',1,3)-1) 
aa1_4
  6   from (select '1;aaa;44455;23;' aa1 from dual)
  7  /

AA1             AA1_1           AA1_2           AA1_3           AA1_4
--------------- --------------- --------------- --------------- ---------------
1;aaa;44455;23; 1               aaa             44455           23

Reviews
thanx :)   August 8, 2009 - 4am Central time zone Bookmark | Bottom | Top Reviewer: ali adel from jordan really thank you very much you are the best man really its a usefull way

---

Write a Review