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

Parse a delimited string in Toad for Oracle

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
LBOYLE4
Asked:
LBOYLE4
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
LBOYLE4Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
What are the rules for generating the id?

Using my example above with two rows in the table, what are the expected results?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LBOYLE4Author Commented:
They are primary keys to a main table.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
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
 
LBOYLE4Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
awking00Commented:
You think you get confused, wait until you get to my age :-)
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now