REGEXP_REPLACE 정규식 표현을 이용한 행변환

DATA 조작을 할때 구분자로 되어 있는 DATA를 조작하기는 쉽지 않다.

복잡하기도 하고 너무 길게 작성하면 가독성도 떨어진다.

REGEXP_REPLACE 사용하여 간단히 작성해 보자!

WITH T_TABLE
     AS (SELECT '1' AS ID
               ,'영희,철수,민수,정철,바보,멍충이,개똥,개똥똥' AS NM
           FROM DUAL
         UNION ALL
         SELECT '2' AS ID
               ,'국수,새롬' AS NM
           FROM DUAL
         UNION ALL
         SELECT '3' AS ID
               ,'영호' AS NM
           FROM DUAL
         UNION ALL
         SELECT '5' AS ID
               ,'아름,딸기,키위' AS NM
           FROM DUAL)
SELECT   ID
        -- ,영희,철수,민수, 첫번째 , 의 위치를 찾고 거기에 1을 더해서 영의 위치값(2)을 찾는다.

        -- 영부터 시작해서 끝나는 , 위치값을 찾기위해

        -- ,철수 2번째 위치한 ,의 위치값(4)을 찾은후 ,영희 첫번째 콤마값(1) 위치를 빼준다

        -- 그렇게 되면 콤마와 콤마 사이의 글자개수가 나오게 된다. 마지막으로 ,를 빼주기 위해 -1을 해준다

        -- SUBSTR(',영희,철수,민수,',2,2)

        -- 복잡 하지만 속도면에서는 정규식 표현보다 더 빠를 수 있다.

        --,SUBSTR (NM,INSTR (NM, ',', 1, RM) + 1,INSTR (NM, ',', 1, RM + 1)- INSTR (NM, ',', 1, RM)-1) AS NM

        ,REGEXP_SUBSTR(NM, '[^,]+', 1, RM) AS NM2
    FROM (SELECT ID
                ,NVL(LENGTH(REGEXP_REPLACE(NM, '[^,]', '')), 0) + 1 AS COMMA_CNT
                --,','|| NM || ',' as NM

                ,NM
            FROM T_TABLE) A
        ,(SELECT     ROWNUM AS RM
                FROM DUAL
          CONNECT BY ROWNUM < 10) B
   WHERE A.COMMA_CNT >= B.RM
ORDER BY ID

 

 * 특정 구분자로 되어 있는 자료를 행으로 나열 하는 기법

-- 사용시 정규식 표현을 사용하면 간단하게 적용 할 수 있다.

-- 확실히 CASE문을 쓴 구문보다 한줄로 작성하는게 더 보기도 좋다.

-- 하지만 속도면에서는 정규식 표현보다 더 빠를 수 있다.

SELECT    
          CASE
               WHEN ROWNUM < DELIM_CNT THEN
                   SUBSTR(STR
                         ,INSTR(STR, '|', 1, ROWNUM) + 1
                         ,INSTR(STR, '|', 1, ROWNUM + 1) - INSTR(STR, '|', 1, ROWNUM) - 1)
               WHEN ROWNUM = DELIM_CNT THEN
                   SUBSTR(STR, INSTR(STR, '|', 1, ROWNUM) + 1)
           END
               AS MDL_CD
          ,ROWNUM
          ,REGEXP_SUBSTR(STR, '[^|]+', 1, ROWNUM) AS NM2
      FROM (SELECT /*+ MO_MERGE */
                  '|' || 'aa|bb|cc' AS STR                 
                  ,NVL(LENGTH(REGEXP_REPLACE('aa|bb|cc','[^|]','')),0) +1   AS DELIM_CNT
              FROM DUAL)
CONNECT BY ROWNUM <= DELIM_CNT