sql - 寄存器 0x​​104567911 PL/SQL)

Question

Oracle 数据库 11g R2 11.2.0.1 SE1

考虑下表地址，它有 3 行

ROW1:aaaaaaaaaaaaaaaaaaaaaaaaaaCRLFbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbCRLFccccccccccccccccCRLFdddddd
ROW2:eeeeCRLFffffffffffffggggggggghCRLF
ROW3:iiCRLFjjjjjjjjjjkkkCRLF

我需要根据以下条件拆分字符串
条件 1:将字符串拆分为 CRLF。
条件2:如果拆分后的字符串长度大于10，则按10个字符的长度拆分该字符串。

注意:一个数据的长度是不固定的。

结果:-
地址
ROW1:aaaaaaaaaa
啊啊啊啊啊啊
aaaaaCRLF
bbbbbbbbbb
bbbbbbbbbb
bbbbbbbbbb
bbCRLF
cccccccccc
cccccCRLF
滴滴答答
ROW2:eeeeCRLF
ffffffffff
ffgggggggg
hCRLF
第三行:iiCRLF
jjjjjjjjjj
kkkCRLF

Answer 1

此答案基于此处的技术:https://stackoverflow.com/a/29338715/4311

首先，我们将在名为 data 的 CTE 中设置您的示例数据。然后查询根据 CRLF 的出现将此文本拆分为行，这为我们提供了 data2。

然后我们在结果字符串中每 10 个字符获取并添加一个 CRLF，这给了我们 data3。最后，我们再次重复原来的过程，根据产生 data4 的 CRLF 将文本拆分成行。

with data as
(select 'row1' rowval, 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || chr(13) || chr(10) || 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' || chr(13) || chr(10) || 'ccccccccccccccc' || chr(13) || chr(10) || 'ddddd' txt from dual union all
 select 'row2' rowval, 'eeee' || chr(13) || chr(10) || 'ffffffffffffggggggggh' || chr(13) || chr(10) txt from dual union all
 select 'row3' rowval, 'ii' || chr(13) || chr(10) || 'jjjjjjjjjjkkk' || chr(13) || chr(10) txt from dual
)
select rowval,
       txt
  from (select rowval,
               trim(regexp_substr(txt, '[^' || CHR(13)||CHR(10) || ']+', 1, lines.column_value)) txt
          from (select rowval,
                       regexp_replace(txt,
                                      '(.{10})',
                                      '\1' || CHR(13) || CHR(10)) txt
                  from (SELECT rowval,
                               trim(regexp_substr(data.txt, '[^' || CHR(13)||CHR(10) || ']+', 1, lines.column_value)) txt
                          FROM data,
                               TABLE(CAST(MULTISET(SELECT LEVEL
                                                     FROM dual
                                                   CONNECT BY instr(data.txt, CHR(13)||CHR(10), 1, LEVEL - 1) > 0
                                                  ) AS sys.odciNumberList
                                         )
                                    ) lines
                        ) data2
               ) data3,
               TABLE(CAST(MULTISET(SELECT LEVEL
                                             FROM dual
                                           CONNECT BY instr(data3.txt, CHR(13)||CHR(10), 1, LEVEL - 1) > 0
                                          ) AS sys.odciNumberList
                                 )
                         ) lines
       ) data4
 where txt is not null
ORDER BY rowval, txt;

这个查询产生了我们:

ROWV TXT
---- ------------
row1 aaaaaa
row1 aaaaaaaaaa
row1 aaaaaaaaaa
row1 bb
row1 bbbbbbbbbb
row1 bbbbbbbbbb
row1 bbbbbbbbbb
row1 ccccc
row1 cccccccccc
row1 ddddd
row2 eeee
row2 ffffffffff
row2 ffgggggggg
row2 h
row3 ii
row3 jjjjjjjjjj
row3 kkk