Link to home
Start Free TrialLog in
Avatar of LBOYLE4
LBOYLE4

asked on

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

Avatar of LBOYLE4

ASKER

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
What are the rules for generating the id?

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

ASKER

They are primary keys to a main table.
>>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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LBOYLE4

ASKER

Thanks. I am not generating the ID. It already exist, I just need to pull it over in my table with the parsed values.
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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...  ;)
You think you get confused, wait until you get to my age :-)
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.