如何在Oracle中使用和分隔符将CLOB对象拆分为多个记录 [英] How to split a CLOB object using , and : delimiter in Oracle into multiple records

问题描述

我有一个CLOB对象示例，如下所示.我想首先使用定界符，"将其拆分，然后将其保存在临时表中，以备后用.

I have a CLOB object sample as shown below. I want to first split this by using delimiter "," and save it in a temporary table for later use.

ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0

我想将结果以以下格式保存在每一行中.

I want to save the result in the below format in each row.

Column_Name
__________________________
ABCDEF:PmId12345RmLn1VlId0
ABCDEF:PmId12345RmLn1VlId0
ABCDEF:PmId12345RmLn1VlId0

我尝试使用REGEXP_SUBSTR函数

I tried using REGEXP_SUBSTR function

select 
    regexp_substr('ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0', '[^,]+', 1, 1) Column_Name 
from dual;

上面的查询给了我一条像下面的记录

The above query gives me single record like below

Column_Name
__________________________
ABCDEF:PmId12345RmLn1VlId0

谁能帮我解决这个问题.

Can anyone help me solve this issue.

推荐答案

以下是使用递归分解子查询(Oracle 11.2及更高版本)的解决方案:

Here is a solution using a recursive factored subquery (Oracle 11.2 and above):

with inputs ( str ) as (
       select to_clob('ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0')
       from dual
     ),
     prep ( s, n, token, st_pos, end_pos ) as (
       select ',' || str || ',', -1, null, null, 1
         from inputs
       union all
       select s, n+1, substr(s, st_pos, end_pos - st_pos),
              end_pos + 1, instr(s, ',', 1, n+3)
         from prep
         where end_pos != 0
     )
select n as idx, token as column_name
from   prep
where  n > 0;



   IDX COLUMN_NAME
------ ----------------------------
     1 ABCDEF:PmId12345RmLn1VlId0
     2 ABCDEF:PmId12345RmLn1VlId0
     3 ABCDEF:PmId12345RmLn1VlId0
     4 ABCDEF:PmId12345RmLn1VlId0
     5 ABCDEF:PmId12345RmLn1VlId0

注释:

您说过CLOB，但是在您的示例中，您是从varchar2字符串中提取的.我添加了to_clob()来查看它是否/如何在CLOB上工作.

You said CLOB but in your example you extracted from a varchar2 string. I added to_clob() to see if/how this works on a CLOB.

我使用instr和substr，因为它们通常(通常是?)的性能要好于regexp等效物.

I used instr and substr, as they often (usually?) perform between better and much better than their regexp equivalents.

我将每个子字符串的索引"保存在输入字符串中；在某些情况下，输入字符串中标记的顺序很重要. (不过，在您的示例中，您只是将相同的令牌重复了五次.)

I saved the "index" of each substring within the input string; in some cases the order of the tokens in the input string is important. (Not in your example though, you just had the same token repeated five times.)

如果您需要更好的性能，尤其是如果CLOB非常大，则最好使用dbms_lob.substr和dbms_lob.instr-请参阅

If you need better performance, especially if your CLOBs are very large, you may be better off using dbms_lob.substr and dbms_lob.instr - see Performance of SUBSTR on CLOB, especially Alex Poole's answer, and documentation here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABEAJAD. Note the syntax differences vs regular substr / instr.

这篇关于如何在Oracle中使用和分隔符将CLOB对象拆分为多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！