#15 15일차_정규 표현식, 그룹 함수

15일차에 배운 내용을 정리하였다.

목차는 다음과 같다. :

 

01. 정규 표현식

 - 01 REGEXP_REPLACE

 - 02 REGEXP_SUBSTR

 - 03 REGEXP_INSTR

 - 04 REGEXP_LIKE

 - 05 REGEXP_COUNT

 

02. 그룹 함수

 - 01 GROUPING SETS

 - 02 ROLLUP

 - 03 CUBE

---

01. 정규 표현식

 - 01 REGEXP_REPLACE

regexp_replace(대상, 찾을문자열[, 바꿀문자열][, 검색위치][, 발견횟수][, 옵션]);

 

 정규식을 이용한 문자열 치환 / 삭제에 사용된다.

  - 바꿀문자열 생략 시 삭제 처리(default : null)
  - 검색위치 생략 시 처음부터 검색 (default : 1)
  - 발견횟수 생략 시 모든 대상 치환/삭제(default : 0)
  - 옵션 생략 시 대소문자 구분(default: c)
  - 대소문자 구분 안하려면 i 전달 필요

 

예제) 숫자 3개 이상 반복되는 문자열과 그 앞의 영문자열의 반복을 모두 삭제하시오.(대소구분x)

select txt, 
       regexp_replace(txt, '[a-z]\d{3}', null, 1, 0, 'i') as 치환1,   -- 숫자 3개 반복
       regexp_replace(txt, '[a-z]+\d{3,}', null, 1, 0, 'i') as 치환2 -- 숫자 3개 이상 반복
  from regexp_test;

 

 

 

예제) txt에서 모든 특수기호를 삭제하시오.

  - \W : 특수기호(_ 제외), 공백포함

  - [:punct:] : _ 포함한 특수기호

 

select txt,
       regexp_replace(txt, '[[:punct:]]'),
       regexp_replace(txt, '\W|_')          -- 공백까지 포함됨(\W : 공백, 특수기호(_제외)) / 이번 예제에서는 권장되지 x
  from regexp_test;

 

 

예제) txt에서 특수기호를 하나 이상 갖는 값을 모두 삭제하시오.

 

select txt,
       regexp_replace(txt, '.*[[:punct:]].*')
  from regexp_test;

 

 

예제) 아래 테이블의 이름의 두번째 글자를 마스킹 처리하시오.(#으로 변경)

 

with tab1 as(
select '김나나' as name
  from dual
 union all
select '홍길동' as name
  from dual
)
select name, 
       substr(name,2,1),
       replace(name, substr(name, 2, 1), '#') as 마스킹1,
       substr(name,1,1)||'#'||substr(name,-1) as 마스킹2,
       regexp_replace(name, '[가-힝]', '#', 1, 2) as 마스킹3
 from tab1;

 

 

예제) STUDENT에서 국번을 ####으로 치환하시오.

 

select tel,
       regexp_replace(tel, '\d+', '####', 1, 2)
  from student;

 

---

 - 02 REGEXP_SUBSTR

regexp_substr(대상, 패턴[, 검색위치][, 발견횟수][, 옵션][, 추출그룹]);

 

 - 패턴 : 추출 패턴 전달(정규식 전달)
 - 검색위치 생략 시 처음부터 패턴을 찾아 추출(default : 1)
 - 발견횟수 : 생략 시 처음 발견되는 패턴만 추출(default : 1)
 - 옵션 : c(default) 또는 i 
 - 추출그룹 : ()로 서브 그룹 지정 시 추출할 그룹 번호 전달

 

예제) 숫자가 3회 이상 반복되는 문자열 앞에 영문자가 1개 이상 반복되는 문자열 포함하여 추출하시오.

 

select txt, regexp_substr(txt, '[a-zA-Z][0-9]{3}') as 추출1,
       regexp_substr(txt, '[a-zA-Z]+[0-9]{3,}') as 추출2
  from regexp_test;

 

 

예제) student 테이블에서 전화번호의 지역번호, 국번, 마지막 네 자리 각각 따로 추출하시오.

 

select tel,
       regexp_substr(tel, '\d+') as 지역번호,
       regexp_substr(tel, '\d+', 1, 2) as 국번,
       regexp_substr(tel, '\d+', 1, 3) as "마지막 네 자리"
  from student;  

 

 또는

 

select tel,
       regexp_substr(tel, '(\d+)\)(\d+)-(\d+)',1,1,null,1) as 지역번호,
       regexp_substr(tel, '(\d+)\)(\d+)-(\d+)',1,1,null,2) as 국번,
       regexp_substr(tel, '(\d+)\)(\d+)-(\d+)',1,1,null,3) as "마지막 네 자리"
  from student;

 

 

예제) professor 테이블의 email에서 이메일 아이디, 도메인을 각각 추출하시오.(그룹번호 전달)

 

select email,
       regexp_substr(email, '([a-z0-9_-]+)@([a-z.]+)',1,1,'i',1) as 이메일아이디,
       regexp_substr(email, '([a-z0-9_-]+)@([a-z.]+)',1,1,'i',2) as 도메인
  from professor;

 

또는

 

select email,
       regexp_substr(email, '(.+)@(.+)',1,1,null,1) as 이메일아이디,
       regexp_substr(email, '(.+)@(.+)',1,1,null,2) as 도메인
  from professor;

 

 

 

 ** 서브그룹 해석순서
   (  ()  ()  () )    (  ()  ()  ()  )
  1  2   3   4    5   6   7   8

 - 바깥쪽의 큰 괄호가 1번, 내부의 괄호를 2, 3, 4  순으로 해석하고 다음에 오는 큰 괄호를 5로 해석한다. 이후 6,7,8 순으로 해석한다.

 

select '2026/03/25 12:00:53',
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,1) "1번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,2) "2번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,3) "3번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,4) "4번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,5) "5번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,6) "6번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,7) "7번",
       regexp_substr('2026/03/25 12:00:53', 
       '((\d+)/(\d+)/(\d+)) ((\d+):(\d+):(\d+))',1,1,null,8) "8번"
  from dual;

 

---

 - 03 REGEXP_INSTR

regexp_instr(대상, 패턴[, 검색위치][, 발견횟수][, 출력옵션][, 옵션][, 추출그룹]);

 

 - 검색위치 : 생략 시 처음부터 검색
 - 발견횟수 : 생략 시 가장 처음 발견되는 문자열의 위치 리턴
 - 출력옵션 : 생략 시 찾은 문자열의 시작위치 리턴(default:0)
           출력옵션이 1인 경우 찾은 문자열의 마지막 위치 그 다음 위치 리턴

 

예제) student 테이블의 전화번호(tel)에서 지역번호 시작위치를 각각 구하기

 

select tel,
       regexp_instr(tel, '\d+') as "지역번호 시작위치",
       regexp_instr(tel, '\d+', 1, 2) as "지역번호 시작위치",
       regexp_instr(tel, '\d+', 1, 1, 1) as "지역번호 끝위치+1"
  from student;

 

 

문제) 다음 결과는?

 

select regexp_instr('500 oracle parkway, redwood shores, ca',
                     '[^ ]+', 1, 2) as result
  from dual;

 

---

 - 04 REGEXP_LIKE

where regexp_like(대상, 패턴[, 옵션]);

 

 WHERE 절에서만 사용 가능
 해당 패턴(정규식)을 만족하는 행만 선택하기 위해 사용
 기존 LIKE 연산자의 와일드카드(%, _)는 메타문자로 해석되지 않는다.

 

예제 ) txt에 값이 a 또는 b로 시작하지 않는(대소구분없이) 행만 출력하시오.

 

select *
  from regexp_test
 where not regexp_like(txt, '^[ab]', 'i');

select *
  from regexp_test
 where regexp_like(txt, '^[^ab]', 'i'); -- 25번 null은 출력되지 X
 

---

 - 05 REGEXP_COUNT

regexp_count(대상, 찾을문자열[, 검색위치][, 옵션]);

 

예제) professor 테이블의 ID 열에서 문자열 세기

 

select id,
       regexp_count(id, '\d') as 결과1,
       regexp_count(id, '\d+') as 결과2
 from professor;

 

---

02. 그룹 함수

여러 집계 결과를 동시에 다양하게 출력하기 위해 사용하는 함수이며, group by 절에서만 사용 가능하다.

 

 - 01 GROUPING SETS

1) group by grouping sets(A, B, C)
 -> group by A + group by B + group by C 

 

2) group by grouping sets(A, (A, B))
 -> group by A + group by A, B 

 

3) group by grouping sets(A, ())
 -> group by A + 전체집계결과 (()은 NULL로 표현 가능)

 

예제) emp 테이블에서 부서별 평균급여와 전체평균급여를 동시에 출력하시오.

 

-- union all)
select deptno, round(avg(sal)) as 급여평균
  from EMP
 group by deptno
 union all
 select null, round(avg(sal))
   from emp;

-- grouping sets)
select deptno, round(avg(sal)) as 급여평균
  from EMP
 group by grouping sets(deptno, null);

 

 

예제) emp 테이블에서 부서별, 직무별 평균급여 + 부서별 평균급여 + 전체평균급여를 출력하시오.

 

-- union all)
select deptno, job, round(avg(sal)) as 평균급여 
  from emp
 group by deptno, job
 union all
select deptno, null, round(avg(sal))
  from emp 
 group by deptno
 union all
select null, null, round(avg(sal))
  from emp
 order by 1,2;

-- grouping sets)
select deptno, job, round(avg(sal))
  from emp 
 group by grouping sets(deptno, (deptno, job), null);

 

 

예제)student 테이블에서 학년별+성별별 키 평균, 성별별 전체 키 평균을 함께 출력하시오.

 

-- union all)

select grade, decode(substr(jumin, 7, 1), '1', '남자', '여자'), round(avg(height))
  from student
 group by grade, decode(substr(jumin, 7, 1), '1', '남자', '여자')
 union all
select null, decode(substr(jumin, 7, 1), '1', '남자', '여자'), round(avg(height))
  from student
 group by decode(substr(jumin, 7, 1), '1', '남자', '여자')
 order by 1,2;
 
-- grouping sets)

select grade, decode(substr(jumin, 7, 1), '1', '남자', '여자'), round(avg(height))
  from student
 group by grouping sets((grade, decode(substr(jumin, 7, 1), '1', '남자', '여자')), 
                         decode(substr(jumin, 7, 1), '1', '남자', '여자'))
 order by 1,2;

 

 

중요)
grouping sets(A, B) : (A) + (B)
grouping sets(A, B, C) : (A) + (B) + (C)
grouping sets(A, (A,B)) : (A) + (A,B)
A, grouping sets(B, NULL) : (A) + (A,B)

---

 - 02 ROLLUP

rollup(A,B) : (A,B) + (A) + NULL
rollup(A,B,C) : (A,B,C) + (A,B) + (A) + NULL
A, rollup(B) : A((B) + NULL)) = (A,B) + (A)
A, rollup(B,C) : A((B,C) + (B) + NULL) = (A,B,C) + (A,B)+ (A)

 

* 오른쪽에서 왼쪽으로 말아올린다. = 오른쪽에서 왼쪽으로 소거한다.
 - 뒤에서부터 컬럼을 소거하면서의 집합 나열
 - 컬럼 나열 순서 중요

 

select deptno, job, sum(sal) as 급여합
  from emp 
 group by rollup(deptno, job);

 

 

select deptno, job, sum(sal) as 급여합
  from emp 
 group by deptno, rollup(job);

 

---

 - 03 CUBE

cube(A,B) : (A,B) + (A) + (B) + NULL
cube(A,B,C) : (A,B,C) + (A,B) + (A,C) + (B,C) + (A) + (B) + (C) + NULL
A, cube(B) : A((B) + ()) = (A,B) + (A)
A, cube(B,C) : A((B,C) + (B) + (C) + ()) = (A,B,C) + (A,B) + (A,C) + NULL

 

 - 모든 발생 가능한 조합 출력
 - 컬럼 나열 순서는 중요하지 않다.

 

select deptno, job, sum(sal) as 급여합
  from emp 
 group by cube(deptno, job);
 

select deptno, job, sum(sal) as 급여합
  from emp 
 group by deptno, cube(job); 

 

---

 - 04 집계 결과 분류 함수 (grouping)

 grouping (컬럼) : 각 컬럼값의 NULL이 집계결과에 의해 출력되는 NULL 여부 확인 (참:1, 거짓:0)

 

select case when grouping(deptno) = 1 and grouping(job) = 0
            then  nvl(to_char(deptno), '소계')
            else to_char(deptno)
        end as deptno,
       job,
       sum(sal),
       grouping(deptno), grouping(job)
  from emp
 group by cube(deptno, job);

 

 

---