如何在clob中找到一段文本并用所需的文本包裹

Question

我有一个存储文本的表，我需要找到找到某些文本的所有位置，然后在其之前和之后添加文本为了更具体，我需要更改"物种"模式发生的所有位置，找到之前(或行的开始(和下一个';''的空间。并用文字包装。

即。转换这个 -

TextAboutSomethingToFind.ExampleA;

进入这个 -

/* Caution
  TextAboutSomethingToFind.ExampleA;
Caution */

我有以下周期来搜索我要寻找的文字的所有记录。

for x in (select t.id
                     ,t.clobvalue
              from table_name t
            where REGEXP_like(t.clobvalue, 'SomethingToFind')
            )
loop
  --Some Operations
end loop;

clobvalue中的文本示例：

BlahBlah Blah;  
TextAboutSomethingToFind.ExampleA; 
ExtraText; MoreExtraText;EvenMoreExtraText;   
LastExtraText;  
TextAboutSomethingToFind.ExcibitB; TextAboutSomethingToFind.PatternС;  
EndText;  

所需结果：

 BlahBlah Blah;
/* Caution
TextAboutSomethingToFind.ExampleA; 
Caution */
ExtraText; MoreExtraText; EvenMoreExtraText;  
LastExtraText;  
/* Caution
TextAboutSomethingToFind.ExcibitB; 
Caution */ 
/* Caution
TextAboutSomethingToFind.PatternС;
Caution */
EndText;

我知道我需要以某种方式使用regexp_replace，但是我不知道如何正确执行此操作。我该怎么做？

Answer 1

这是一个简化的选项，可与源表一起使用单行；如果必须适用于行，事情会变得更加复杂(并不是说不能完成，但是此代码看起来甚至更糟(。

另外，它假定每个"子字符串"(行(可以转换为VARCHAR2(使用TO_CHAR(。

样本数据：

SQL> select * from test;
COL
--------------------------------------------------------------------------------
BlahBlah Blah;
TextAboutSomethingToFind.ExampleA;
ExtraText; MoreExtraText;EvenMoreExtraText;
LastExtraText;
TextAboutSomethingToFind.ExcibitB; TextAboutSomethingToFind.Pattern?;
EndText;

解决方案，书面我的方式。按照代码中的注释。如果您不确定它的作用，请通过CTE执行CTE并观察SELECT s返回的每个值。

SQL> with temp2 as
  2    -- split multi-lines column to rows, separated by CHR(10)
  3    (select level lvl2,
  4            to_char(regexp_substr(col, '[^' || chr(10) ||']+', 1, level, 'm')) col2
  5     from test
  6     connect by level <= regexp_count(col, chr(10)) + 1
  7    ),
  8  temp3 as
  9    -- out of all rows from the previous step, split those - that contain "SomethingToFind"
 10    -- more than once - into their own separate rows
 11    (select lvl2,
 12            level lvl3,
 13            trim(to_char(regexp_substr(col2, '[^;]+', 1, level))) col3
 14     from (select lvl2,
 15                  col2 from temp2
 16           where instr(col2, 'SomethingToFind') > 0
 17             and regexp_count(col2, ';') > 1
 18          )
 19     connect by level <= regexp_count(col2, ';')
 20    ),
 21  almost_there as
 22    -- apply the "Caution" clause to all "SomethingToFinds" from both cases
 23    (select lvl2,
 24            to_number(null) lvl3,
 25            case when instr(col2, 'SomethingToFind') > 0 then
 26                      '/* Caution' ||chr(10) || col2 || chr(10) || 'Caution */'
 27                 else col2
 28            end col
 29     from temp2
 30     where lvl2 not in (select lvl2 from temp3)
 31     union all
 32     select lvl2,
 33            lvl3,
 34            case when instr(col3, 'SomethingToFind') > 0 then
 35                      '/* Caution' ||chr(10) || col3 || chr(10) || 'Caution */'
 36                 else col3
 37            end col
 38     from temp3
 39    )
 40  -- Finally, put them together using LISTAGG, separated by CHR(10)
 41  select listagg(col, chr(10)) within group (order by lvl2, lvl3) result
 42  from almost_there;
RESULT
--------------------------------------------------------------------------------
BlahBlah Blah;
/* Caution
TextAboutSomethingToFind.ExampleA;
Caution */
ExtraText; MoreExtraText;EvenMoreExtraText;
LastExtraText;
/* Caution
TextAboutSomethingToFind.ExcibitB
Caution */
/* Caution
TextAboutSomethingToFind.Pattern?
Caution */
EndText;

SQL>