Hi,
I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a different Column on this same table for that row. Please see the example below.
Long Character, (EXPLANATION FIELD)
ORIG CO NAME=SEA14 - 1730 Min,ORIG ID=9352818303,DESC DATE=INDOFF,ENTRY DESCR=PAYMENTS,ENTRY CLASS=CCD,TRACE NO=021000026215345,ENTRY DATE=181226,IND ID NO=EFT-00414033,IND NAME=EFT-00414033,COMPANY DATA=181224 WWK2JV I,REMARK=EFT/ACH CREATED OFFSET FOR ORIGIN#:9303220000 CO EFF DATE: 18/12/26
Table: MANUFACTURE_ACCOUNT
ACCOUNT # ACCOUNT NAME EXPLANATION FIELD ORIG ID ENTRY DESCR DESC DATE 11111 SEA14 The Long Field 9352828303 PAYMENTS INDOFF
So the above example shows how the stripping and saving needs to work.
Thanks so much!
Ok, starting over. totally ignore above.
I'm including the sample table and some rows to insert. I'm trying to write the PL/SQL that goes through every row and takes the data from Description and updates the tbl_students columns with the necessary data per each studentid. So, the course, title, and course_id is populated based on what exists as a part of DESCRIPTION field.
CREATE TABLE tbl_Students (
Studentid number NOT NULL,
Firstname varchar2(200) ,
Lastname varchar2(200) ,
description varchar2(400),
course varchar2(12),
title varchar2(12),
course_id numeric
);
Insert into tbl_Students (Studentid,Firstname, lastname, description)
Values(1111,'Vivek', 'Johari', 'course=SEA14 - 1730 Min,title=9352818303,course_id=1212');
Insert into tbl_Students (Studentid,Firstname, lastname, description)
Values(1112,'Pankaj', 'Kumar', 'course=SEA15 - 1731 Min,title=6552818303,course_id=1213');
Insert into tbl_Students (Studentid,Firstname, lastname, description)
Values(1113,'Amit', 'Singh', 'course=SEA14 - 1732 Min,title=4352818303,course_id=1214');
commit;
You'll need to use some form of substr to extract out the relevant values. If you're feeling brave you could use regular expressions:
select substr ( regexp_substr ( description, 'course=([^,]*)'), 8 ) course,
substr ( regexp_substr ( description, 'title=([^,]*)'), 7 ) title,
substr ( regexp_substr ( description, 'course_id=(.*)'), 11 ) course_id
from tbl_Students;
COURSE TITLE COURSE_ID
SEA14 - 1730 Min 9352818303 1212
SEA15 - 1731 Min 6552818303 1213
SEA14 - 1732 Min 4352818303 1214
Then update the relevant columns.
Though you'll need to adjust the logic for course - the field doesn't fit in your column limit:
update tbl_Students
set course = substr ( regexp_substr ( description, 'course=([^,]*)'), 8 ),
title = substr ( regexp_substr ( description, 'title=([^,]*)'), 7 ),
course_id = substr ( regexp_substr ( description, 'course_id=(.*)'), 11 );
ORA-12899: value too large for column "CHRIS"."TBL_STUDENTS"."COURSE" (actual: 16, maximum: 12)