Break column value after , using oracle and insert into the table using stored procedure

Question

I have a query where a column name SINGLE_COLO_SAPID has values given in below image.

Most of the time the column contains null and in some place there are 2-3 values separated by comma.

I tried for one comma but in some rows there are 3-4 values. So I want to break it and move it as a new row in that column.

Below is what I tried.

INSERT INTO NE_STRUCTURES (RJ_SAPID, RJ_COLO_SAPID) 
SELECT RJ_SAPID, 
RTRIM(LTRIM(REPLACE(RJ_COLO_SAPID, RJ_SAPID, ''), ','), ',') AS SINGLE_COLO_SAPID ,
        '',
        '',
        INVENTORY_STATUS_CODE,          
        RJ_MAINTENANCE_ZONE_CODE       ,
        RJ_SITE_NAME                   ,
        RJ_SITE_ADDRESS                ,
        RJ_STRUCTURE_TYPE              ,
        TYPE_NAME                      ,
        RJ_LAST_MODIFIED_BY            ,
        RJ_LAST_MODIFIED_DATE          ,
        RJ_STATUS                      ,
        RJ_CITY_CODE                   ,
        RJ_R4G_STATE_CODE              ,
        RJ_DISTRICT_CODE               ,
        RJ_TALUK_CODE                  ,
        RJ_JC_CODE                     ,
        RJ_JIOPOINT_SAPCODE            ,
        RJ_COMPANY_CODE_1              ,
        RJ_COMPANY_CODE_2              ,
        NE_STATUS                      ,
        PLACEMENT_DATE 
FROM NE_STRUCTURES 
WHERE RJ_SAPID IS NOT NULL;

The solution is the same as in the attached answer. What is that you couldn't understand from it? — Kaushik Nayak, Jun 11 '19 at 11:00
My issue is it will take a lot of time to break and process will be too slow — Nad, Jun 11 '19 at 11:03
Check the 2nd answer attached. The answer to the question clearly shows the various options you could use and performance of each method. `recursive approach without regex` also suggested. Try and experiment with those and find what suits you. As I told in the past, until you're stuck with this design, the problems like this are going to continue..I wish you good luck. — Kaushik Nayak, Jun 11 '19 at 11:08
@KaushikNayak while trying the answer i am getting error as `ORA-00904: "T"."VALUE": invalid identifier` the update one in **MTO**'s answer — Nad, Jun 11 '19 at 11:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194753/discussion-between-b-n-and-kaushik-nayak). — Nad, Jun 11 '19 at 11:27
See https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/ — Lalit Kumar B, Jun 11 '19 at 13:22

Popeye · Accepted Answer · 2019-06-12T05:52:31.607

You can use something like the following:

DROP TABLE TTT;

CREATE TABLE TTT (
    VALUESS   VARCHAR2(100),
    ME        VARCHAR2(100)
);

INSERT INTO TTT VALUES (
    '1,2,3',
    'YES'
);

INSERT INTO TTT VALUES (
    '7,8,9',
    'NO'
);

-- query to convert comma separated column value to rows
SELECT DISTINCT
    REGEXP_SUBSTR(VALUESS, '[^,]', 1, LEVEL) AS YOUR_FINAL_COL,
    ME AS OTHER_COLUMNS
FROM
    TTT
CONNECT BY
    LEVEL <= REGEXP_COUNT(VALUESS, ',') + 1 ORDER BY 1;

EDITED QUERY

I have incorporated my logic into your query:

INSERT INTO NE_STRUCTURES (
    RJ_SAPID,
    RJ_COLO_SAPID
)
    WITH DATAS AS (
        SELECT
            RJ_SAPID,
            RTRIM(LTRIM(REPLACE(RJ_COLO_SAPID, RJ_SAPID, ''), ','), ',') AS SINGLE_COLO_SAPID,
            '',
            '',
            INVENTORY_STATUS_CODE,
            RJ_MAINTENANCE_ZONE_CODE,
            RJ_SITE_NAME,
            RJ_SITE_ADDRESS,
            RJ_STRUCTURE_TYPE,
            TYPE_NAME,
            RJ_LAST_MODIFIED_BY,
            RJ_LAST_MODIFIED_DATE,
            RJ_STATUS,
            RJ_CITY_CODE,
            RJ_R4G_STATE_CODE,
            RJ_DISTRICT_CODE,
            RJ_TALUK_CODE,
            RJ_JC_CODE,
            RJ_JIOPOINT_SAPCODE,
            RJ_COMPANY_CODE_1,
            RJ_COMPANY_CODE_2,
            NE_STATUS,
            PLACEMENT_DATE
        FROM
            NE_STRUCTURES
        WHERE
            RJ_SAPID IS NOT NULL
    )
    SELECT DISTINCT
        RJ_SAPID,
        REGEXP_SUBSTR(SINGLE_COLO_SAPID, '[^,]', 1, LEVEL) SINGLE_COLO_SAPID_FINAL,
        '',
        '',
        INVENTORY_STATUS_CODE,
        RJ_MAINTENANCE_ZONE_CODE,
        RJ_SITE_NAME,
        RJ_SITE_ADDRESS,
        RJ_STRUCTURE_TYPE,
        TYPE_NAME,
        RJ_LAST_MODIFIED_BY,
        RJ_LAST_MODIFIED_DATE,
        RJ_STATUS,
        RJ_CITY_CODE,
        RJ_R4G_STATE_CODE,
        RJ_DISTRICT_CODE,
        RJ_TALUK_CODE,
        RJ_JC_CODE,
        RJ_JIOPOINT_SAPCODE,
        RJ_COMPANY_CODE_1,
        RJ_COMPANY_CODE_2,
        NE_STATUS,
        PLACEMENT_DATE
    FROM
        DATAS
    CONNECT BY
        LEVEL <= REGEXP_COUNT(SINGLE_COLO_SAPID, ',') + 1;

TRY TO EXECUTE ONLY SELECT QUERY as INSERT will not work. It is copy pasted from your question itself

Thanks tejash, but i am getting error as `SQL Error: ORA-00904: "VALUESS": invalid identifier` — Nad, Jun 11 '19 at 11:22
Please check now. I forgot to replace `VALUESS` with `SINGLE_COLO_SAPID` in my previous update. I have updated query now. — Popeye, Jun 12 '19 at 04:33
giving error as `SQL Error: ORA-00913: too many values 00913. 00000 - "too many values"` — Nad, Jun 12 '19 at 05:19
Yes, Because in insert statement there are only two columns and in select there are many. I have just copy pasted your `INSERT` query. You can just execute `SELECT` and you will find your answer. You can then use that according to your need. — Popeye, Jun 12 '19 at 05:50
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194798/discussion-between-b-n-and-tejash-ora-00980). — Nad, Jun 12 '19 at 05:51
Please add all columns related to SELECT query in your INSERT. — Popeye, Jun 12 '19 at 05:54
Tejash, if you dont mind can you come on chat. so we can discuss more effectively — Nad, Jun 12 '19 at 05:54

Break column value after , using oracle and insert into the table using stored procedure

1 Answers1