使用oracle中断列值，然后使用存储过程将其插入表中 [英] Break column value after , using oracle and insert into the table using stored procedure

问题描述

我有一个查询，其中列名SINGLE_COLO_SAPID具有下图中给出的值.

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

大多数情况下，该列包含空值，并且在某些地方有2-3个值，以逗号分隔.

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

我尝试了一个逗号，但是在某些行中有3-4个值.因此，我想将其断开并将其作为该列中的新行移动.

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.

下面是我尝试过的.

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;

推荐答案

您可以使用以下内容:

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;

编辑查询

我已将我的逻辑合并到您的查询中:

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;

尝试执行仅选择查询，因为插入将不起作用.它是从您的问题本身粘贴而来的副本

这篇关于使用oracle中断列值，然后使用存储过程将其插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！