×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Dividing the text data into rows
2

Dividing the text data into rows

Dividing the text data into rows

(OP)
Hi,

98765,AB123,3;98765,AD243,2;98765,BG153,5;

The above text data would have the following fields ID, SKU, QTY followed by ;
There could be multiple records with all the 3 fields in a line with ; separator.
I want to push the data the into a table in the following manner. i.e. row wise with # of quantity broken into 1.
So therefore the 1st record has 3 Qty, it will be broken into 3 records/rows with ID & SKU repeated.

ID SKU QTY
98765 AB123 1
98765 AB123 1
98765 AB123 1

98765 AD243 1
98765 AD243 1

98765 BG153 1
98765 BG153 1
98765 BG153 1
98765 BG153 1
98765 BG153 1


How can I identify the separator, divide # of quantity to multiple rows to be used in stored procedure?
I'm using Oracle 11g.

TIA,
RAJ

RE: Dividing the text data into rows

Here's a quick and dirty crack at it that should get you pretty close:

CODE

CREATE OR REPLACE
PROCEDURE breaking_and_entering(p_string IN VARCHAR2) IS
   l_id   NUMBER;
   l_idx1 INTEGER;
   l_idx2 INTEGER;
   l_qty  INTEGER;
   l_sku VARCHAR2(100);
   l_substr VARCHAR2(1000);   
BEGIN
   l_idx1 := 1;
   l_idx2 := INSTR(p_string, ';',l_idx1);
   WHILE (l_idx2 != 0) LOOP
      -- BREAK OUT THE FIRST SUBRECORD
      l_substr := SUBSTR(p_string,l_idx1, l_idx2 - l_idx1);
      -- BREAK OUT THE RECORD ELEMENTS
      l_id := SUBSTR(l_substr, 1,INSTR(l_substr,',')-1);
      l_sku := SUBSTR(l_substr,INSTR(l_substr,',')+1, INSTR(l_substr,',',-1)- INSTR(l_substr,',')-1);
      l_qty := SUBSTR(l_substr,INSTR(l_substr,',',-1)+1,LENGTH(l_substr));
      -- CREATE THE INSERT RECORDS
      FOR i IN 1..l_qty LOOP
         INSERT INTO my_table VALUES (l_id,l_sku,1);
      END LOOP;
      -- GET READY FOR THE NEXT RECORD
      l_idx1 := l_idx2 + 1;
      l_idx2 := INSTR(p_string, ';',l_idx1);
    END LOOP;
EXCEPTION
   WHEN OTHERS THEN 
      -- REPLACE THE FOLLOWING WITH HOWEVER YOU WANT TO HANDLE AN EXCEPTION
      NULL;
END breaking_and_entering; 

RE: Dividing the text data into rows

(OP)
Thanks Carp. Let me try it out & will update you.

RE: Dividing the text data into rows

(OP)
Hi Carp,
I did try the code, works fine. Need to add some more of checks before inserting into the table.
Wondering why this simple code didn't strike me.
Thanks once again.

Thanks,
RAJ

RE: Dividing the text data into rows

rk68,
The best way to say: "Thank you" here at TT is to award somebody a star for their help, which you only did twice in last 16 years. Don't you find the help here useful ponder


---- Andy

There is a great need for a sarcasm font.

RE: Dividing the text data into rows

(OP)
Sorry Andy, I didn't note that. Hence forth I will definitely do.
Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close