[SQL] 비 규칙적인 글자 정규식 사용 추출

with z1 as(

SELECT regexp_extract(A.ARY,'[^,]+', 1, cast(c.cnt as int),'u') as ACT_CD
 FROM (SELECT regexp_REPLACE(ARRAY_COMBINE(regexp_extract_all(액팅정보컬럼, '(\)|\:\d\d)\/[가-힣A-Z\s?!+-]','u'), ','), '\)\/|\:\d\d\/', '','u') as ARY
	    FROM 투약테이블
	  ) A
 cross join (SELECT row_number () OVER (order by 1) as cnt    
              FROM (SELECT 1 FROM (SELECT  regexp_REPLACE(ARRAY_COMBINE(regexp_extract_all(액팅정보컬럼, '(\)|\:\d\d)\/[가-힣A-Z\s?!+-]','u'), ','), '\)\/|\:\d\d\/', '','u') as ARY			 					  FROM 투약테이블
								  )F  
				   LIMIT 70 
				   ) V 
			 ) C

 WHERE 액팅코드 IS NOT NULL

)


SELECT 액팅코드, count(*)
FROM z1
GROUP BY 액팅코드
;

데이터 예시: 

액팅정보

8:00/Y

8:00/Y, 9:00/Y

8:00/, 9:00/Y

8:00/Y, 9:00/N

원하는 출력값:

액팅코드	건수
Y	5
NULL	1
N	1

 

참고: http://nz2nz.blogspot.com/2016/09/netezza-transpose-delimited-string-into.html?m=1