ORACLE - split string and populate Q

ORACLE 10G

I have a table with a string in the format for example.......      '00001000090003D

I have a view that has been created from that table to show split the string up like this

col2    col2      col3 col4
00001 00009  003  D

How would I incorporate into my view to get it so that I can populate a 5th col (col5) with the contents from col2, col3, col4 and have the data separated by a . or -

thanks

Jimbo
jim_bob_jimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Docteur_ZCommented:
select col1||'-'||col2||'-'||col3||'-'||col4 "col5" from yourtable
in your view

or if you want it "inline" :
update yourtable set col5=col1||'-'||col2||'-'||col3||'-'||col4
if each field is caracters.
0
jim_bob_jimAuthor Commented:
within my table col1 is '00001000090003D'

my view has that column split up into 4 different columns using the SUBSTR command

I need to be able to within my view select a 5th coloumn and have in that column the data from col1, col3, col4 all separated by a '.' or '-' Im not interested with col2

I had already tried your method but I think it is failing cause it needs to select from the columns within the view

any thoughts
0
TaygerCommented:
Should work that way (dont know the exact substr-pos):

Create or replace view xyz
select subsr(0,4) col1,
           substr (4,8) col2,
           substr (8,12) col3,
           substr (12,16) col4,
           subsr(0,4) ||'-'|| substr (8,12)  ||'-'|| substr (12,16)          -- col5 (= col1, col3, col4)
from orig_table

you need to reselect the data from the orig table for the column you want it.
Greetings
Tayger



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TaygerCommented:
sorry, mistake in substr ('whole_string'),pos, length)....

Docteur Z's way should work as well, easier way... You can concatenate several columns of the same record and displaying it next to single column values.
Tayger
0
morphmanCommented:
Alternatively, create a view-on-a-aview

create view myview2 as
select a.*, col1 || '.' || col3 || '.' || col4 as col5 from myview;
0
TaygerCommented:
Hello again

IMHO, just putting a view on a view is not really recommandable for shwoing a additional column.
If the showed possible are not the solution you are looking for you maybe put a view display example how you would like to have it(?).
Tayger
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.