在oracle中，将字符串分解为固定长度的记录

Question

如何截断这样一个字符串:

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。路易斯*莫* 631013736 ~ ~ N1 * PE * ABC公司

变成固定长度的80个字符?——blob可以有空格，大小从80个字符到80,000个字符，没有固定的分隔符。

谢谢。

Answer 1

你可以这样做:

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;

使用regexp_substr获取1-80个字符，使用connect by level获取结果块作为行。返回的行数是字符串的长度除以80，四舍五入。

如果你想在pl/sql中做一些处理，你可以这样做:

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;

Answer 2

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;