在Oracle中通过固定长度拆分文本将一个行拆分成多个行

Question

我有一张包含如下数据的表格：

CREATE TABLE UDA_DATA 
( uda VARCHAR2(20), 
value_text VARCHAR2(4000) 
); 

Insert into UDA_DATA values('Material_ID','PBL000129 PBL000132 PBL000130 PBL000131 PBL000133'); 
Insert into UDA_DATA values('Material_ID','PBL0001341 PBL0001381 PBL0001351 PBL0001361 PBL0001371'); 
   commit;

现在，如果我们从这个表中选择数据，它将会给出类似于以下的结果：

select * from UDA_DATA;

它会给出类似于以下内容的结果：

但是我期望的是这样的：

意思是如果value_text的字符长度超过30，则应将其分成两行或更多行。此外，uda列应该有后缀1、2..n，并且不应在文本之间断开。

编写了一个递归CTE来实现这个结果:

with rcte (rn, uda, value, chunk_num, value_text) as (
  select rownum,
    uda,
    substr(value_text, 1, 30),
    1,
    substr(value_text, 31)
  from uda_data
  union all
  select rn,
    uda,
    substr(value_text, 1, 30),
    chunk_num + 1,
    substr(value_text, 31)
  from rcte
  where value_text is not null
)
select uda || chunk_num as uda, value
from rcte
order by rn, chunk_num;

这会产生如下结果：

在第三行，它把文字分开了，这是不正确的，希望得到以下结果：

任何帮助都将不胜感激。

- Shashank Jha

---

regexp_replace(value_text,'(.{1,30}+)\s','\1,') 用适当的逗号替换了需要更改的空格。 - Sayan Malakshinov

Answer 1

以下是一种方式：

• 将字符串拆分为单词
• 重新组合单词，每个组合长度不超过30个字符

这些行的uda值相同，所以我先给它们每个一个row_number，以便您可以区分它们。

我使用了match_recogonize来将长度不超过30个字符的行分组，然后使用listagg在每个组中重新组合单词。

您也可以使用递归来实现此操作。

具体操作如下：

with ranks as (
  select u.*,
         row_number () over (
           order by uda, value_text
         ) rk
  from   uda_data u
), rws as (
  select rk, uda, rn,
         regexp_substr ( value_text, '[^ ]+', 1, rn ) || ' ' str
  from   ranks, lateral (
    select level rn from dual
    connect by level <= regexp_count ( value_text, ' ' ) + 1
  ) 
), grps as (
  select *
  from   rws
    match_recognize (
      partition by rk
      order by rn
      measures
        match_number() as grp,
        sum ( length ( str ) ) as len
      all rows per match 
      pattern ( thirty+ )
      define 
        thirty as sum ( length ( str ) ) <= 30
    )
)
  select uda || grp, 
         listagg ( str ) 
           within group ( order by rn ) strs 
  from   grps
  group by rk, uda || grp;
  
UDA||GRP        STRS                             
Material_ID1    PBL000129 PBL000132 PBL000130     
Material_ID2    PBL000131 PBL000133               
Material_ID1    PBL0001341 PBL0001381             
Material_ID2    PBL0001351 PBL0001361             
Material_ID3    PBL0001371 

注意：拆分-重组技巧会增加您处理的行数。如果输入字符串很长，这种方法可能非常慢。如果大多数将拆分成2-3个组，则可能可以 - 当然要在您的数据上进行测试！

- Chris Saxon

---

嗨，Chris，非常感谢你的时间和帮助。你的解决方案对我很有效。 - Shashank Jha

Answer 2

您查询的问题在于，虽然您想要按空格拆分字符串，但您甚至没有尝试这样做。相反，您使用了substr（value_text，1，30），它不关心空格在字符串中的位置。

您可以使用INSTR查找拆分位置。以下查询有效，只要其中没有超过30个字符的代码（在这种情况下，INSTR返回-1，这会导致递归查询中的循环）。您可能需要调整此查询以处理此情况。

with rcte (rn, uda, value, chunk_num, value_text) as (
  select rownum,
    uda,
    substr(trim(value_text), 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
    1,
    trim(substr(trim(value_text), instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
  from uda_data
  union all
  select rn,
    uda,
    substr(value_text, 1, instr(substr(value_text || ' ', 1, 31), ' ', -1) - 1),
    chunk_num + 1,
    trim(substr(value_text, instr(substr(value_text || ' ', 1, 31), ' ', -1) + 1))
  from rcte
  where value_text is not null
)
select uda || rn || '/' || chunk_num as uda, value
from rcte
order by rn, chunk_num;

示例：https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8dfc8e55a12c4666b4bc7bfcaceea2d2

- Thorsten Kettner

---

嗨Thorsten，非常感谢你的时间和帮助。你的解决方案对我很有效。 - Shashank Jha

Answer 3

1. 将长度最长为30的空格替换为逗号：

select
  u.*
 ,regexp_replace(value_text,'(.{1,30}+)\s','\1,') modified
from uda_data u;

结果：

UDA                  VALUE_TEXT                                                   MODIFIED
-------------------- ------------------------------------------------------------ --------------------------------------------------------------------------------
Material_ID          PBL000129 PBL000132 PBL000130 PBL000131 PBL000133            PBL000129 PBL000132 PBL000130 PBL000131,PBL000133
Material_ID          PBL0001341 PBL0001381 PBL0001351 PBL0001361 PBL0001371       PBL0001341 PBL0001381 PBL0001351 PBL0001361,PBL0001371

分割逗号：

select *
from uda_data u, 
     xmltable(
         'ora:tokenize(concat(",",.),",")[position()>1]'
         passing regexp_replace(value_text,'(.{1,30}+)\s','\1,')
         columns 
            n for ordinality,
            v varchar2(100) path '.'
         );

结果：

UDA                  VALUE_TEXT                                                            N V
-------------------- ------------------------------------------------------------ ---------- ---------------------------------------------
Material_ID          PBL000129 PBL000132 PBL000130 PBL000131 PBL000133                     1 PBL000129 PBL000132 PBL000130 PBL000131
Material_ID          PBL000129 PBL000132 PBL000130 PBL000131 PBL000133                     2 PBL000133
Material_ID          PBL0001341 PBL0001381 PBL0001351 PBL0001361 PBL0001371                1 PBL0001341 PBL0001381 PBL0001351 PBL0001361
Material_ID          PBL0001341 PBL0001381 PBL0001351 PBL0001361 PBL0001371                2 PBL0001371

- Sayan Malakshinov

---

很棒的解决方案。如果Oracle有像PostgreSQL一样的regexp_split_to_table函数就更好了。但由于它还没有，所以很高兴能像你展示的那样绕过这个问题。 - Thorsten Kettner

@ThorstenKettner，Oracle中有很多不同的字符串拆分解决方案。它甚至拥有内置函数，如dbms_utility.comma_to_table或xmltable/xmlquery（tokenize）等，但通常最好为此创建自己的定制流水线函数。 - Sayan Malakshinov