Solved

Parse a delimited string in Toad for Oracle

Posted on 2013-01-03
13
418 Views
Last Modified: 2013-10-27
How can a parse a delimited string in Toad for Oracle that will give me the values in a column in a table.  For example 1234_3455_3445, would need to be parsed into
1234
3455
3455

I will need to map them to a parent record.  So I need to parse them so I can write a query for a second piece of this project.  So my ultimate goal would to be able to parse the records into a table like this

Column 1  Parse row
Parent1     1234
Parent1      2345
Parent1     4563
Parent2      1123
Parent2       44563
0
Comment
Question by:LBOYLE4
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740202
Parsing the column value into rows is pretty straight forward.  There is a working example below.

I'm not following the Parent1 piece of the question.  Can you explain a little more about what you want?

Parse column to row example:
drop table tab1 purge;
create table tab1(col1 varchar2(40));

insert into tab1 values('1234_3455_3445');
insert into tab1 values('1111_2222');
commit;

select rtrim(substr(col1,(column_value*5)-4,5),'_') parse_row from tab1, 
 table(
  cast(
  multiset(select level from dual connect by level <= length(col1)-length(replace(col1,'_'))+1)
  as sys.odcinumberlist
  )
  )
/

Open in new window

0
 

Author Comment

by:LBOYLE4
ID: 38740286
I want to be able to put in the table two fields.  The Key value related to the parsed rec and the parsed values.

ID  ParsedRec
1     1234
2      2354
3      456
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740297
What are the rules for generating the id?

Using my example above with two rows in the table, what are the expected results?
0
 

Author Comment

by:LBOYLE4
ID: 38740305
They are primary keys to a main table.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740322
>>They are primary keys to a main table.

So they need to be unique for hte single execution or unique over time?

In other words: You start with my two rows, you end up with 1-5.  Then what happens the next time you run this?  Do you need to start with 6?

Might I suggest an Oracle sequence to take care of this for you?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6015.htm#SQLRF01314
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 38740332
Assuming you want to take the values from a 'master' table and parse them into some other table, check out the updated example.

It takes the delimited data from tab1 and uses a sequence to load the data into tab2.

drop table tab1 purge;
create table tab1(col1 varchar2(40));

drop sequence my_seq;
create sequence my_seq;

insert into tab1 values('1234_3455_3445');
insert into tab1 values('1111_2222');
commit;

drop table tab2 purge;
create table tab2(id number, col1 varchar2(40));

insert into tab2
select my_seq.nextval, rtrim(substr(col1,(column_value*5)-4,5),'_') parse_row from tab1, 
 table(
  cast(
  multiset(select level from dual connect by level <= length(col1)-length(replace(col1,'_'))+1)
  as sys.odcinumberlist
  )
  )
/

commit;

select * from tab2;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:LBOYLE4
ID: 38740375
Thanks. I am not generating the ID. It already exist, I just need to pull it over in my table with the parsed values.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38740428
So, do you need any additional assistance with this?

If so, I'll need more information.  I'm still not following the Parent1 aspect of the requirements.

Is there another table you need to join?

Sample data and expected results help a lot.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 38741406
slightwv, I think LBOYLE4 just needs to add the id to your query -
select id, rtrim(substr(col1,(column_value*5)-4,5),'_') parse_row from tab1,
 table(
  cast(
  multiset(select level from dual connect by level <= length(col1)-length(replace(col1,'_'))+1)
  as sys.odcinumberlist
  )
  )
;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38741419
>>I think LBOYLE4 just needs to add the id to your query -

I think you may be right!  Told you I was confused on the rest of the requirements...  ;)
0
 
LVL 31

Expert Comment

by:awking00
ID: 38741453
You think you get confused, wait until you get to my age :-)
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39603602
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now