Regexp_Substr를 사용해 구분자를 통한 row생성

Oracle 10g부터 나온 기능으로

문자열에서 정규 표현식 패턴을 검색하여 추출되는 문자를 반환합니다. REGEXP_SUBSTR은 SUBSTRING 함수 함수와 비슷하지만 문자열에서 정규 표현식 패턴을 검색할 수 있습니다.

REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )

 

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/REGEXP_SUBSTR.html

REGEXP_SUBSTR 함수 - Amazon Redshift

---

"오라클 콤마 row" 이런 식으로 검색을 했을 때

https://jong138.blogspot.com/2017/05/oracle-row.html

오라클 Oracle 콤마(,) 문자열을 행(row)으로 분리

 

컬럼에  'AAA,BBB,CCC,DDD' 라는 값이 있다면,

오라클 10G

Select Regexp_Substr('AAA,BBB,CCC,DDD','[^,]+', 1, Level) From Dual
Connect By Regexp_Substr('AAA,BBB,CCC,DDD', '[^,]+', 1, Level) Is Not Null

오라클 9i

SELECT     SUBSTR (TEST,
                   INSTR (TEST, ',', 1, LEVEL) + 1,
                   INSTR (TEST, ',', 1, LEVEL + 1) - INSTR (TEST, ',', 1, LEVEL)
                   - 1
                  ) AS ROWDATA
FROM (SELECT ',' || 'AAA,BBB,CCC,DDD' || ',' AS TEST
              FROM DUAL)
CONNECT BY LEVEL <= LENGTH (TEST) - LENGTH (REPLACE (TEST, ',')) - 1

결과

---

 

Regexp_Substr , Level , Connect By를 사용한 예제들이 많이 나온다.

나도 처음에는 저 방식으로 사용하려고 해보려고 했는데

마지막에 그룹바이를 하면서 계속 돌기만 하길래 뭔가 잘못되었구나 싶었고 

팀장님께 질문을 드렸다.. '-'

 

with temp as (

    select 데이터1
    from TABLE
    where use_yn = 'Y' -- 등 조건 작성

), temp2 as (        
        select Regexp_Substr( 데이터1 , '[^,]+',1,Level) as 데이터1
        from temp 
        connect by Regexp_Substr(데이터1 , '[^,]+',1,Level) is not null
)

이런 식으로 모두 잘라서 ROW로 만들고
SELECT 데이터1 ,COUNT(*)
FROM TEMP2
GROUP BY 데이터1;

이런 식으로 작성했었는데 
마지막에 데이터1의 값별로 모아서 개수를 알고 싶었다.

근데 여기서 1분 넘게 기다려도 결과가 나오지 않다. ( 최종 결과 데이터가 2만개가 넘어서 그런가 싶기도 했지만.. )

 

무튼 팀장님께서 

직관적인 게 더 좋은 방법이라고 하시면서 방법을 말씀해주셨는데

사용하려는 컬럼의 값을 필요한 모양으로 만들고 (여기서는 리플레이스 사용)

REGEXP_SUBSTR를 통해 구분자(여기서는 콤마)로 자름

그리고 유니온 올을 써서

콤마로 구분했을 때 1번 값

콤마로 구분했을 때 2번 값

콤마로 구분했을 때 3번 값

콤마로 구분했을 때 4번 값

을 모두 출력한다. (내 경우는 해당 칼럼의 데이터를 콤마로 구분했을 경우 최대 4개나왔음.)

 

select 
    데이터1, 
    count(*) cnt 
from 
    ( 
    select 
        * 
    from 
        ( 
        select 
            REGEXP_SUBSTR(replace(replace(replace(데이터1,' ',''),', ',','),' ,',','), '[^,]+', 1, 1) AS 데이터1  
        from 
            테이블 
        where 
            use_yn='Y' -- 등 필요한 조건 
            
        
        UNION ALL
        
        
        select 
            REGEXP_SUBSTR(replace(replace(replace(데이터1,' ',''),', ',','),' ,',','), '[^,]+', 1, 2) AS 데이터1  
        from 
            테이블 
        where 
            use_yn='Y' -- 등 필요한 조건 
            
            
         UNION ALL
        
        
        select 
            REGEXP_SUBSTR(replace(replace(replace(데이터1,' ',''),', ',','),' ,',','), '[^,]+', 1, 3) AS 데이터1  
        from 
            테이블 
        where 
            use_yn='Y' -- 등 필요한 조건     
            
            
         UNION ALL
        
        
        select 
            REGEXP_SUBSTR(replace(replace(replace(데이터1,' ',''),', ',','),' ,',','), '[^,]+', 1, 4) AS 데이터1  
        from 
            테이블 
        where 
            use_yn='Y' -- 등 필요한 조건 
            
            
        ) a1 
    where 
        length(a1.데이터1) = 2 -- 추가 조건 
    ) b1 
group by 
    데이터1 
order by cnt desc