Solved

Parse a delimited string in Toad for Oracle

Posted on 2013-01-03
13
423 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
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: 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
 

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 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 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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Retrieve Values 4 66
Query Records that don't match 8 42
join 2 views with 5 conditions 3 54
'G_F01' is not a procedure or is undefined 3 23
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 how to recover a database from a user managed backup

813 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

12 Experts available now in Live!

Get 1:1 Help Now