How to split a CLOB object using , and : delimiter in Oracle into multiple records

Question

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

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.

score 9 · Accepted Answer · edited May 23 '17 at 11:44

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

Notes:

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.

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.)

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.

Thanks for the response. This works perfect and it returns CLOB object. Can I return string out of it? — Usha, Jul 19 '16 at 10:19
That would be another benefit of using `dbms_lob.substr`; it takes a `CLOB` input string and it spits out a `VARCHAR2`. Interestingly, this is documented, while I wasn't able to quickly see in the Oracle documentation where it says standard `substr` on a `CLOB` returns `CLOB` (but of course I believe you). — , Jul 19 '16 at 12:18
You are brilliant to share so powerful SQL, save me lot time as we got an issue of string toooo long when using REGEXP_SUBSTR to split a large varchar, thank you... — meadlai, Jan 24 '19 at 09:00
This is not working if the string is of length of 10,000 characters. Any other way? — TechTurtle, Sep 25 '20 at 22:24

score -1 · Answer 2 · answered Jul 15 '16 at 13:53

Just in case you really are just trying to parse a long string as per your example. Include "level" in the select if you need to see the value's index in the list:

SQL> with tbl(str) as (
     select 'ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmI
d12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:P
mId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0,ABCDEF:PmId12345RmLn1VlId0'
       from dual
   )
   select regexp_substr(str, '(.*?)(,|$)', 1, level, NULL, 1) column_name
   from tbl
   connect by regexp_substr(str, '(.*?)(,|$)', 1, level) is not null;

COLUMN_NAME
--------------------------------------------------------------------------------
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

16 rows selected.

SQL>

This gives a performance issue when we use connect by clause. — Usha, Jul 19 '16 at 10:21
This was in case you were really working with a string instead of a clob. — Gary_W, Jul 19 '16 at 12:07

How to split a CLOB object using , and : delimiter in Oracle into multiple records

2 Answers2

Linked