[SQL] 오라클 - 분할 다중 콤마 여러 행 Oracle 테이블의 값을 구분

오라클 - 분할 다중 콤마 여러 행 Oracle 테이블의 값을 구분

나는 오라클 분할 쿼리에 문제가 있습니다.

분할 쉼표 오라클 쿼리로 연결하고 정규 표현식을 사용하여 여러 행에 데이터를 분리하는 동안 나는 더 많은 중복 행을 얻고있다. 실제로 예를 들어 내 표는 쉼표를 가진 한 개의 행이 난 단지 155 행을 얻을 수있다 그래서 전체 문자열을 분리 점에서 150 개 행을 가지고하지만 난 2000 개 행을 얻고있다. 나는 그것의 작동 벌금을 별개의 사용하지만 경우 내가 쿼리 결과에서 중복 행을 돼요.

나는 쿼리 결과에서 중복 행을 생성하는 것 그러나 다음 쿼리를 시도했다 :

WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
              UNION 
              SELECT 'f,g',2 from dual
              UNION 
               SELECT 'h',3 FROM DUAL)

SELECT TRIM(REGEXP_SUBSTR( TEMP, '[^,]+', 1, LEVEL)) ,SLNO FROM CTE 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(temp, '[^,]+')) + 1

편집하다

여러 행이있는 테이블에서 실행하면 위의 선택 쿼리는 하나의 쉼표로 구분 된 문자열을 분할 할 수 있습니다, 그러나, 중복 행을 생성합니다. 어떻게 중복 행을 제한하는?

해결법

1. ==============================

   1.마지막으로 나는이 답변을 내놓았다

   마지막으로 나는이 답변을 내놓았다

   WITH CTE AS (SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL
                 UNION
                 SELECT 'f,g' temp, 2 slno FROM DUAL
                 UNION
                 SELECT 'h' temp, 3 slno FROM DUAL)
   SELECT TRIM(REGEXP_SUBSTR(temp, '[^,]+', 1, level)), slno
   FROM CTE
   CONNECT BY level <= REGEXP_COUNT(temp, '[^,]+')
       AND PRIOR slno = slno
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
   

2. ==============================

   2.이 같은 시도,

   이 같은 시도,

   WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
                 UNION 
                 SELECT 'f,g',2 from dual
                 UNION 
                 SELECT 'h',3 FROM DUAL)
        SELECT regexp_substr (temp, '[^,]+', 1, rn)temp, slno
        FROM   cte
        CROSS JOIN 
        (
             SELECT ROWNUM rn
             FROM  (SELECT MAX (LENGTH (regexp_replace (temp, '[^,]+'))) + 1 max_l
                    from cte
                    )
             connect by level <= max_l
            )
        WHERE regexp_substr (temp, '[^,]+', 1, rn) IS NOT NULL
        order by temp;
   

3. ==============================

   3.에 의해 연결 사용하지 않고 :

   에 의해 연결 사용하지 않고 :

   WITH CTE AS (SELECT 'a,b,c,d,e' temp,1 slno  FROM DUAL
         UNION 
         SELECT 'f,g',2 from dual
         UNION 
          SELECT 'h',3 FROM DUAL
   )
   ,x as (
     select
     ','||temp||',' temp
     ,slno
     from CTE
   )
   ,iter as (SELECT rownum AS pos
       FROM all_objects
   )
   select
   SUBSTR(x.temp
     ,INSTR(x.temp, ',', 1, iter.pos) + 1
     ,INSTR(x.temp, ',', 1, iter.pos + 1)-INSTR(x.temp, ',', 1, iter.pos)-1
   ) temp
   ,x.slno
   from x, iter
   where iter.pos < = (LENGTH(x.temp) - LENGTH(REPLACE(x.temp, ','))) - 1;
   

4. ==============================

   4.허용 대답은 부적절한 상태 DBMS_RANDOM.VALUE IS NOT NULL을 사용한다. 그것은 단지 그러나 정직하고 질문하는 방법으로 올 것이다 때 dbms_random.VALUE 널이 될 수 있으며, 순환 루프를 방지? 논리적으로는 NULL이 될하지 않습니다.

   허용 대답은 부적절한 상태 DBMS_RANDOM.VALUE IS NOT NULL을 사용한다. 그것은 단지 그러나 정직하고 질문하는 방법으로 올 것이다 때 dbms_random.VALUE 널이 될 수 있으며, 순환 루프를 방지? 논리적으로는 NULL이 될하지 않습니다.

   더 적절한 용액을 사용 sys.odciNumberList 순환 루프를 방지하기위한 것이다.

   예를 들어,

   설정

   SQL> CREATE TABLE t (
     2    ID          NUMBER GENERATED ALWAYS AS IDENTITY,
     3    text        VARCHAR2(100)
     4  );
   
   Table created.
   
   SQL>
   SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');
   
   1 row created.
   
   SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');
   
   1 row created.
   
   SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');
   
   1 row created.
   
   SQL> COMMIT;
   
   Commit complete.
   
   SQL>
   SQL> SELECT * FROM t;
   
           ID TEXT
   ---------- ----------------------------------------------
            1 word1, word2, word3
            2 word4, word5, word6
            3 word7, word8, word9
   
   SQL>
   

   필수 쿼리 :

   SQL> SELECT t.id,
     2         trim(regexp_substr(t.text, '[^,]+', 1, lines.column_value)) text
     3  FROM t,
     4    TABLE (CAST (MULTISET
     5    (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1)
     6                 AS sys.odciNumberList
     7                )
     8          ) lines
     9  ORDER BY id
    10  /
   
           ID TEXT
   ---------- --------------------------------------------------
            1 word1
            1 word2
            1 word3
            2 word4
            2 word5
            2 word6
            3 word7
            3 word8
            3 word9
   
   9 rows selected.
   

   XMLTABLE을 사용하는 다른 솔루션 :

   SQL> SELECT id,
     2         trim(COLUMN_VALUE) text
     3  FROM t,
     4    xmltable(('"'
     5    || REPLACE(text, ',', '","')
     6    || '"'))
     7  /
   
           ID TEXT
   ---------- ------------------------
            1 word1
            1 word2
            1 word3
            2 word4
            2 word5
            2 word6
            3 word7
            3 word8
            3 word9
   
   9 rows selected.
   
   SQL>
   

   모델 절과 같은 작업을 달성하는 방법은 여러 가지가 있습니다. 더 많은 예제를 참조하십시오 분할 쉼표 테이블에서 문자열을 구분

5. ==============================

   5.변환 쉼표 쿼리 아래를 사용할 수 행에 값을 분리

   변환 쉼표 쿼리 아래를 사용할 수 행에 값을 분리

    SELECT trim(x.column_value.extract('e/text()')) COLUMNS
    from t t, table (xmlsequence(xmltype('<e><e>' || replace(valuestring,':','</e><e>')||  
    '</e></e>').extract('e/e'))) x   );  
   

6. ==============================

   6.독특한 절을 추가하면 트릭을 수행합니다

   독특한 절을 추가하면 트릭을 수행합니다

       WITH cte AS (
           SELECT 'a,b,c,d,e' temp, 1 slno FROM DUAL UNION 
           SELECT 'f,g',2 FROM DUAL UNION SELECT 'h',3 FROM DUAL
       ) SELECT UNIQUE(slno),REGEXP_SUBSTR(temp,'[^,]+', 1, LEVEL)temp FROM cte
       CONNECT BY LEVEL<=REGEXP_COUNT(temp, '[^,]+') ORDER BY slno;
   

from https://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows by cc-by-sa and MIT license