DATOR


REGEXP.토큰파싱.가로세로.행열.in변수 SQL


가로데이터 <==> 세로데이터

 

데이터를 다루다 보면 가로를 세로로, 세로를 가로로,  자유롭게 변환해야 하는 경우가 있다.

 

가로로 배열된 형태의 1건 데이터를 세로 ROW형태로 M건 생성하는 사례를 살펴 본다.  데이터 이행, 프로그램작성, 데이터조작, 엑셀가공 등 활용할 기회가 많이 있으니 몇개 정도는 이곳에 기록해 놓고 두고두고 사용하려 한다.  이런 SQL을 외우기는 힘들다.   Copy해서 내것으로 만들자.

 

Input 문자 :  1개의 컬럼에 4개의 정보가 있다.  각 문자는 " , " 구분자로 분리되어 있다

 A,BB,CCC,DDD

 

Output 문자 : 정보갯수 (토큰수) 만큼 ROW수를 발생시킨다.

A
BB
CCD
DDDD
 
1 )  복잡하지만 INSTR, LENGTH, SUBSTR를 사용해서 SQL을 사용해 볼 수 있다
SELECT
  CHK_WORD
, CHK_WORD_COPY
, T.NO1
, LENGTHB(CHK_WORD) - LENGTHB(REPLACE(CHK_WORD,',')) + 1 AS WORD_CNT   -- 4개 단어 갯수를 센다.
, INSTR(CHK_WORD_COPY, ',', 1, T.NO1)+ 1  AS OFFSET1                   -- ','가 시작되는 Point
, INSTR(CHK_WORD_COPY, ',', 1, T.NO1 + 1 )  -  INSTR(CHK_WORD_COPY, ',', 1, T.NO1)- 1  AS OFFSET2 -- 시작 Point에서의 문자길이
, SUBSTR(CHK_WORD_COPY ,INSTR(CHK_WORD_COPY, ',', 1, T.NO1)+1
           ,INSTR(CHK_WORD_COPY, ',', 1, T.NO1 + 1 )  -  INSTR(CHK_WORD_COPY, ',', 1, T.NO1)- 1 ) AS WORD -- ','마다 문자를 파싱한다.
FROM (
          SELECT CHK_WORD, ',' || CHK_WORD || ',' AS CHK_WORD_COPY  -- 데이터 가공을 쉽게 하기위해 앞 뒤에 ','를 추가
          FROM ( SELECT  'A,BB,CCC,DDDD' CHK_WORD FROM DUAL  )      -- Input 변수 값
      ) W,
      (SELECT LEVEL NO1 FROM DUAL CONNECT BY LEVEL <=100 ) T        -- Input 변수 갯수가 최대 100개로 세팅
WHERE (LENGTHB(CHK_WORD) - LENGTHB(REPLACE(CHK_WORD,',')) + 1 )>= T.NO1  -- 갯수만큼만 Join처리

 

)  REGEXP_SUBSTR과 REGEXP_REPLACE를 사용하면 단순해 진다.

SELECT W.*
       ,REGEXP_SUBSTR(CHK_WORD,'[^,]+',1, CP.NO1) AS WORD              -- ','  기준으로 1부터, WNO 번째 문자를 잘라내라.
FROM
(
    SELECT CHK_WORD,
           NVL(LENGTH(REGEXP_REPLACE(CHK_WORD,'[^,]+'))+1,1) WD_CNT    --',' 구분자를 제외한 문자 갯수를 센다.
    FROM  ( SELECT  'A,BB,CCC,DDDD' CHK_WORD FROM DUAL )
    ) W
   ,(SELECT LEVEL NO1 FROM DUAL CONNECT BY LEVEL <=100) CP
WHERE W.WD_CNT >= CP.NO1    -- 문자 갯수 만큼 Join



------------------------------------------------------------------------------

다음은 REGEXP_  함수 관련 응용 예시입니다.

------------------------------------------------------------------------------

 

예시 1 : 특정 컬럼에서 문자 혹은 숫자인 것만 검색하고자 하는 경우

 

SELECT WORD,
       REGEXP_REPLACE(WORD,'[0-9]','')      W_CHR,  -- 숫자는 빼고 문자만 나와라
       REGEXP_REPLACE(WORD,'[^0-9]','')     W_NUM   -- 문자는 빼고 숫자만 나와라
FROM (
   SELECT 'ASC_1'  WORD FROM DUAL UNION ALL
   SELECT 'ASC_21' WORD FROM DUAL UNION ALL
   SELECT 'ASC_BDA10'  WORD FROM DUAL UNION ALL
   SELECT 'ASC_BDA8'  WORD FROM DUAL UNION ALL
   SELECT 'ASC_10BDA8'  WORD FROM DUAL UNION ALL
   SELECT '10'  WORD FROM DUAL UNION ALL
   SELECT '33'  WORD FROM DUAL UNION ALL
   SELECT '01'  WORD FROM DUAL
)
WHERE 1=1
  AND REGEXP_REPLACE(WORD,'[0-9]','')  IS  NULL       -- 숫자만 있는 WORD만 검색
  --AND REGEXP_REPLACE(WORD,'[0-9]','')  IS NOT NULL    -- 문자만 있는 WORD만 검색

 

예시 2 : 특정 문자를 다른 문자로 변환

 

SELECT REGEXP_REPLACE('a;bb;ccc;dddd;',  '[^;]+'  , '@') from dual;  -- ';'을 제외한 나머지는 '@'로 변환
SELECT REGEXP_REPLACE('a;bb;ccc;dddd;',  '[;]+'  , '*')  from dual;  -- ';'를 '*'로 변환
SELECT REPLACE('a;bb;ccc;dddd;',  ';'  , '*')  from dual;            -- ';'를 '*'로 변환 : 위와 같음

 

예시 3 : 문자값들을 토큰 파싱하는 또 다른 방식

 

SELECT REGEXP_SUBSTR( 'a;bb;ccc;dddd;', '[^;]+', 1, level )
FROM DUAL
CONNECT BY  REGEXP_SUBSTR( 'a;bb;ccc;dddd;', '[^;]+', 1, LEVEL ) IS NOT  NULL 
-- 1자리부터 레벨갯수의 문자를 파싱함
-- 파싱하다가 마지막에는 NULL이 됨.


 

예시 4 : 멀티 변수값 리스트를 조건으로 입력 >  vs_input = 'A,C,G,E'

          WHERE COL1 IN ( :vs_input) 구문에서 여러개의 IN 문자값을 조립 할 수 있다.

 

SELECT COL1
FROM (SELECT 'A' COL1 FROM DUAL UNION ALL
      SELECT 'B' COL1 FROM DUAL UNION ALL
      SELECT 'C' COL1 FROM DUAL UNION ALL
      SELECT 'D' COL1 FROM DUAL UNION ALL
      SELECT 'E' COL1 FROM DUAL UNION ALL
      SELECT 'F' COL1 FROM DUAL UNION ALL
      SELECT 'G' COL1 FROM DUAL UNION ALL
      SELECT 'H' COL1 FROM DUAL UNION ALL
      SELECT 'I' COL1 FROM DUAL
      ) W
WHERE COL1 IN
    ( SELECT REGEXP_SUBSTR( 'A,C,G,E', '[^,]+', 1, LEVEL )
       FROM DUAL
       CONNECT BY REGEXP_SUBSTR( 'A,C,G,E', '[^,]+', 1, LEVEL ) IS NOT  NULL 
    )  -- 문자 갯수 만큼 IN 조건을 만든다.



 

문자 관련된 파싱 기법 중 가끔 사용하는 구문들을 소개 해 보았습니다.

 

* 따뜻한 데이터 세상 만들기 - FROM 핫신 *
Tag :

Leave Comments