|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
December 28th, 2004, 01:59 PM
|
|||
|
|||
split column into 3 columns
I have a single address columns that is holding address2 and address3 as well with carriage returns. I need the address column split into 3 seperate columns. So data like the following:
address ---------------- Admin Tech Services 1234 Elm Avenue Towne House can display as: address -------- Admin Tech Services address2 --------- 1234 Elm Avenue address3 -------- Towne House
|
|
#2
January 27th, 2005, 08:55 PM
|
|||
|
|||
|
Assuming you know what your carriage returns are (chr(10) in example), this can be done with case statements like the following. If you know every address has 3 lines, you can skip the case and just use the lines in the "else" sections.
Code:
update tbl set address = case when instr(address, chr(10)) = 0 then address else substr(address, 1, instr(address, chr(10))-1) end, address2 = case when instr(address, chr(10)) = 0 then null when instr(address, chr(10), 1, 2) = 0 then substr(address, instr(address, chr(10))+1) else substr(address, instr(address, chr(10))+1, (instr(address, chr(10), 1, 2)-instr(address, chr(10)))-1) end, address3 = case when instr(address, chr(10), 1, 2) = 0 or instr(address, chr(10)) = 0 then null else substr(address, instr(address, chr(10), 1, 2)+1) end
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > split column into 3 columns |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
