Solved

Parse a delimited string in Toad for Oracle

Posted on 2013-01-03
13
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 77

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 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

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 77

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
 

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 77

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 32

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 77

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 32

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

756 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