Блог

У меня есть таблица, которая содержит данные примерно такого вида:

 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;
  

Который дает результат, подобный этому:

В третьей строке это разбиение текста между ними, что неверно, хотите получить результат чего-то такого:

Любая помощь будет высоко оценена.

Вот один из способов:

• Разделите строки на слова
• Рекомбинируйте слова длиной до 30 символов

Строки имеют одинаковые значения для uda , поэтому я также присвоил каждой из них row_number первое значение, чтобы вы могли отличить их друг от друга.

Я использовал match_recogonize для группировки строк длиной до тридцати символов. Затем listagg рекомбинировать слова в каждой группе.

Вы могли бы адаптировать recursive with для этого тоже

Что дает:

 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 группы, это вероятно нормально — хотя, очевидно, протестируйте на своих данных!

Проблема с вашим запросом заключается в том, что, хотя вы хотите разбить строку на пробелы, вы даже не пытаетесь это сделать. Вместо этого вы используете 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_18amp;fiddle=8dfc8e55a12c4666b4bc7bfcaceea2d2

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
  

2. Разделять запятыми:

 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