Home » SQL & PL/SQL » SQL & PL/SQL » splitting multiple values in a column
splitting multiple values in a column [message #1283] Wed, 17 April 2002 05:05 Go to next message
Joe
Messages: 138
Registered: November 1999
Senior Member
I have a column containing multiple (5) values separated by tab. Is it possible to split these values and insert them into another table? I am sure this is possible without creating an external script using perl or the like. Any direction is greatly appreciated.
Re: splitting multiple values in a column [message #1284 is a reply to message #1283] Wed, 17 April 2002 05:43 Go to previous message
Epe
Messages: 99
Registered: March 2002
Member
Hello Joe,

I suppose it's a varchar2 column with multiple values.
You can take a piece of a character based field with the function substr(field_name,start position,length).
You can use this function with a hard coded start-position if the length of the first value is the same for every record, if the length of the second value is the same for every record, ...
If not, you can use the function instr(field_name,character_combination,occurence) or any calculation with the instr() function. It is possible to make quite complicated combinations this way.
Example 1 :
table_A(field1) ----> table_B(f1,f2,f3)
---------------
a b cd
z y xw

insert into table_B(f1,f2,f3)
(select substr(field1,1,1),substr(field1,3,1), substr(field1,6,2) from table_A where ...);

Example 2 :
table_A(field1) ----> table_B(f1,f2,f3)
---------------
a b cd
zy xw v

insert into table_B(f1,f2,f3)
(select substr(field1,1,instr(field1,' ')), substr(field1,instr(field1,' ')+1,instr(field1,' ',2)-instr(field1,' ')+1), substr(field1,instr(field1,' ',2)+1) from table_A where...);

I hope it's a little bit clear. For further help on functions like substr() and instr() see the on-line Oracle Manuals on
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/function.htm#87068

Success,

epe
Previous Topic: Population a table usung a text or excel file
Next Topic: Splitting multiple values in a column separated by tab
Goto Forum:
  


Current Time: Thu Mar 28 14:00:32 CDT 2024