CODEWITHSUNDEEP
sqloracle-databaseplsql
QuickDrawMcgraw
QuickDrawMcgraw

Reputation: 83

Break a string into fixed length records in oracle

How would I break a string such as this:

BPR*D*895.11*CACHCTX*01*062001186*DA*2533167775*0011111114**01*021000089*DA*0007777777*20150317*VEN~TRN*1*1234600*0987654321~~DTM*097*19980205~N1*PR*123 EASY PAY 1*1*123000000~N3*1234 MAIN STREET~N4*ST. LOUIS*MO*631013736~~N1*PE*ABC COMPANY

into fixed lengths of 80 characters each? -- the blob can have spaces, size from 80 characters to 80,000, and no fixed delimiters.

Thank you.

Upvotes: 1

Views: 3695

Answers (2)

Trijit
Trijit

Reputation: 531

declare
l_clob clob:='BPR*D*895.11*CACHCTX*01*062001186*DA*2533167775*0011111114**01*021000089*DA*0007777777*20150317*VEN~TRN*1*1234600*0987654321~~DTM*097*19980205~N1*PR*123 EASY PAY 1*1*123000000~N3*1234 MAIN STREET~N4*ST. LOUIS*MO*631013736~~N1*PE*ABC COMPANY';
l_length number;
l_cnt number:=1;
l_amount number:=80;
type tab_type is table of varchar2(80);
t_tab_type tab_type;
begin
t_tab_type :=tab_type();
l_length:=length(l_clob);

dbms_output.put_line(to_char(l_length));
while l_length>0 loop
t_tab_type.extend;
t_tab_type(l_cnt):=dbms_lob.substr(l_clob,l_amount,l_amount*(l_cnt-1)+1);
dbms_output.put_line(t_tab_type(l_cnt));
l_cnt:=l_cnt+1;
l_length:=l_length-l_amount;
end loop;
dbms_lob.freetemporary(l_clob);
end;

Upvotes: 0

Mark Leiber
Mark Leiber

Reputation: 3138

You could do it like this:

with test as (
  select 'vBPR*D*895.11*CACHCTX*01*062001186*DA*2533167775*0011111114**01*021000089*DA*0007777777*20150317*VEN~TRN*1*1234600*0987654321~~DTM*097*19980205~N1*PR*123 EASY PAY 1*1*123000000~N3*1234 MAIN STREET~N4*ST. LOUIS*MO*631013736~~N1*PE*ABC COMPANY' str
  from dual
)
select regexp_substr(str, '(.{1,80})', 1, level) as chunks
from test
connect by level <= ceil(length(str)/80)
order by level;

This uses regexp_substr to get 1-80 characters and connect by level to get the resulting chunks as rows. The number of rows returned is the length of the string divided by 80, rounded up.

If you want to do some processing in pl/sql, you can use something like this:

begin
    for i in (
        with test as (
          select 'vBPR*D*895.11*CACHCTX*01*062001186*DA*2533167775*0011111114**01*021000089*DA*0007777777*20150317*VEN~TRN*1*1234600*0987654321~~DTM*097*19980205~N1*PR*123 EASY PAY 1*1*123000000~N3*1234 MAIN STREET~N4*ST. LOUIS*MO*631013736~~N1*PE*ABC COMPANY' str
          from dual
        )
        select regexp_substr(str, '(.{1,80})', 1, level) as chunks
        from test
        connect by level <= ceil(length(str)/80)
        order by level
    )
    loop
      dbms_output.put_line('chunk: ' || i.chunks);
      -- do whatever INSERT you want with the value of i.chunks
    end loop;
end;

Upvotes: 4

Related Questions