Extract records by APEX_STRING

I want to share my experience about to extract records by Query, so I am writing this article in order to explain it.

The example in this article rely on the following test table. This sample used (+) for separating between string.

Create table 

create table test_extract(f1 number, f2 varchar2(3000))
/

Insert sample data

insert into test_extract(f1,f2)
            values(100,'MC0051+MC0053+MC0055+MC0059+MC0061+MC0134+MC0135+MC0136+MC0149+MC0150+MC0151')
/
insert into test_extract(f1,f2)            
            values(200,'CR0219+CR0229')
/
commit
/

Extract Query

--Step 1

--simple query

SELECT  f2 AS str,

      f1 as  SRL

FROM test_extract

/

MC0051+MC0053+MC0055+MC0059+MC0061+MC0134+MC0135+MC0136+MC0149+MC0150+MC0151    100

CR0219+CR0229                                                                   200


--Step 2

--merge two fields

SELECT    REPLACE ( str, '+',  ':' || SRL || '+') || ':' || SRL   AS str,

          SRL

FROM (SELECT  f2 AS str,

              f1 as  SRL

      FROM test_extract

     )

/


MC0051:100+MC0053:100+MC0055:100+MC0059:100+MC0061:100+MC0134:100+MC0135:100+MC0136:100+MC0149:100+MC0150:100+MC0151:100    100

CR0219:200+CR0229:200    200


--Step 3

--makes one record

--Here we see a simple example of the LISTAGG function, producing a comma-separated list of string

SELECT LISTAGG (str, ',') WITHIN GROUP (ORDER BY SRL) AS str

FROM (SELECT    REPLACE ( str, '+',  ':' || SRL || '+') || ':' || SRL   AS str,

              SRL

    FROM (SELECT  f2 AS str,

                  f1 as  SRL

          FROM test_extract

         ))

/


MC0051:100+MC0053:100+MC0055:100+MC0059:100+MC0061:100+MC0134:100+MC0135:100+MC0136:100+MC0149:100+MC0150:100+MC0151:100,CR0219:200+CR0229:200


--Step 4

--In this section we create a dataset and nested table

SELECT apex_string.split (REPLACE (str, ',', '+'), '+') AS str

FROM (SELECT LISTAGG (str, ',') WITHIN GROUP (ORDER BY SRL) AS str

      FROM (SELECT    REPLACE ( str, '+',  ':' || SRL || '+') || ':' || SRL   AS str,

                      SRL

            FROM (SELECT  f2 AS str,

                          f1 as  SRL

                  FROM test_extract

                 ))) 

                 

--Step 5

--Final query that we get all records

SELECT REGEXP_SUBSTR (COLUMN_VALUE, '[^:]+', 1, 1) "PARAM_VALUE", REGEXP_SUBSTR (COLUMN_VALUE, '[^:]+', 1, 2)  "SRL"

FROM TABLE (

            SELECT apex_string.split (REPLACE (str, ',', '+'), '+') AS str

            FROM (SELECT LISTAGG (str, ',') WITHIN GROUP (ORDER BY SRL) AS str

                  FROM (SELECT    REPLACE ( str, '+',  ':' || SRL || '+') || ':' || SRL   AS str,

                                  SRL

                        FROM (SELECT  f2 AS str,

                                      f1 as  SRL

                              FROM test_extract

                             )))) 


---result

PARAM_VALUE     SRL

MC0051          100

MC0053          100

MC0055          100

MC0059          100

MC0061          100

MC0134          100

MC0135          100

MC0136          100

MC0149          100

MC0150          100

MC0151          100

CR0219          200


CR0229          200 

I hope to enjoy.