Home » SQL & PL/SQL » SQL & PL/SQL » Row to columns based on delimiter!
Row to columns based on delimiter! [message #292160] Tue, 08 January 2008 01:48 Go to next message
etl_prasad
Messages: 44
Registered: June 2005
Location: Scotland
Member
Hi All,

I want to update the columns in table with data in sigle column row which follows

I have table like this:

col1 col2 col3 col4 col5 col6 col7
abc>xyz>qaz>wsx>qqqq>q

Want to update as folows

col1 col2 col3 col4 col5 col6 col7
abc>xyz>qaz>wsx>qqqq>q abc xyz qaz wsx qqqq q

col1 can have dynamic number of occurences in between 1 to 6 delimited with '>'

i tried searching on 'pivot' and 'row to column' strings i could not able to get the relevent answer to the problem..
can any one please give me suggestion.

thanks in advance Smile

[Updated on: Tue, 08 January 2008 01:50]

Report message to a moderator

Re: Row to columns based on delimiter! [message #292165 is a reply to message #292160] Tue, 08 January 2008 02:03 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Use SUBSTR and INSTR:

col1 = SUBSTR('&&data', 1, INSTR('&&data', '>', 1)-1)
col2 = SELECT SUBSTR('&&data', INSTR('&&data', '>', 1)+1, INSTR('&&data', '>', 2)-1)
Etc.
Previous Topic: problem insert clob with more than 4000 bytes
Next Topic: PL-Sql function
Goto Forum:
  


Current Time: Mon Dec 05 09:17:55 CST 2016

Total time taken to generate the page: 0.22364 seconds