• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3116
  • Last Modified:

ORACLE - split string and populate Q


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 -


1 Solution
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.
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
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.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
Alternatively, create a view-on-a-aview

create view myview2 as
select a.*, col1 || '.' || col3 || '.' || col4 as col5 from myview;
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(?).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now