Skip to Main Content
  • Questions
  • Parsing through a Long Character with 255 characters and Stripping out Words

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Kamran.

Asked: February 06, 2019 - 3:04 pm UTC

Last updated: February 08, 2019 - 1:57 pm UTC

Version: Oracle 12 C

Viewed 1000+ times

You Asked

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;

and Chris said...

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)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PL/SQL Review

Kamran Amini, February 07, 2019 - 1:45 pm UTC

Excellent Reply and if there is a way to include the code as a part of a PL/SQL Procedure that I could run it, that would include the coding and updating of the table, I would greatly appreciate it.
Chris Saxon
February 07, 2019 - 3:25 pm UTC

Just stick the update in a procedure:

create or replace procedure split_desc as
begin
  update students
  set   ...
end p;
/

super (j)sonic

Racer I., February 08, 2019 - 8:56 am UTC

Hi,

How about :

WITH
JSon_Students AS (
  select Studentid, '{"' || REPLACE(REPLACE(description, '=', '":"'), ',', '","') || '"}' json_desc from tbl_Students)
select Studentid,
  json_value(json_desc, '$.course') course,
  json_value(json_desc, '$.title') title,
  json_value(json_desc, '$.course_id') course_id
from   JSon_Students;


Stiff requirements for the input though (no in-value =/:/,/").
One wonders were this data is coming from and if the source can't send it properly typed or at least as validated json.

Also : better normalize-out courses :
tbl_courses(ID NUMERIC, course VARCHAR2(200), title VARCHAR2(200))

regards,
Chris Saxon
February 08, 2019 - 11:08 am UTC

Seems a little convoluted to convert to JSON, then extract to me...

One wonders were this data is coming from and if the source can't send it properly typed or at least as validated json.

That would be the ideal solution.

hyper (j)sonic

Racer I., February 08, 2019 - 1:17 pm UTC

Hi,

> Seems a little convoluted to convert to JSON, then extract to me...

Fair point. Especially for the additional internal processing. Just thought it looked already almost like JSON and Oracle has some (hopefully optimized) convenience methods for this. So I played around a little more :

https://livesql.oracle.com/apex/livesql/s/hx1hvng12c909sti3qcjkp935

WITH 
JSon_Students AS ( 
  select Studentid, TREAT('{' || REPLACE(REPLACE(description, '=', ':"'), ',', '",') || '"}' AS JSON) json_desc from tbl_Students) 
select js.Studentid, js.json_desc.course, js.json_desc.title, js.json_desc.course_id 
from   JSon_Students js

TREAT(AS JSON) requires 18c so via LiveSQL. But this should mean Oracle DOMs each row only once. But maybe even JSON_VALUE would reuse previous ones. Looks nice and you don't have to know the key-lengths.

Side discovery : Oracle allows keys without quotes (but not values). Nice, albeit not JSON-conform.

regards,
Chris Saxon
February 08, 2019 - 1:57 pm UTC

:)


Side discovery : Oracle allows keys without quotes (but not values). Nice, albeit not JSON-conform.

Yep, we allow "lax" JSON. If you only want fully conforming JSON, you can use the

is json strict

constraint.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.