26. 정규식표현(1)

[ 정규식표현(regexp) ]
문자열 패턴을 간소화하여 공통 패턴을 가진 문자열을 추출,삭제,조회하기 위한 표현식

** 정규식표현식
1) 문자/숫자/특수기호 표현
\d : 숫자
\w : 글자(숫자,문자) + _
\s : 공백
\t : 탭
\n : 엔터
[[:digit:]] : 숫자
[[:alpha:]] : 문자
[[:alnum:]] : 숫자+문자
[[:punct:]] : 특수기호
[[:blank:]] : 공백

2) 횟수 표현
* : 0회 이상(포함되어있지 않거나 1회이상 연속적으로 포함된 경우)
    ex) abc\d* : abc, abc1, abc1000처럼 abc 뒤에 숫자가 없거나 있다면 여러개 나열되는 형태의 문자열을 의미
                 abc1000ab 문자열의 경우 abc1000까지만 추출
+ : 1회 이상
    ex) abc\d+ : abc1, abc1000
    
? : 0회 또는 1회
    ex) abc\d? : abc, abc1

{n} : n회 반복 
    ex) abc\d{3} : abc111, abc999
    
{n,} : n회 이상
    ex) abc\d{3,} : abc111, abc9999, abc9999999

{n,m} : n회 이상 m회 이하
    ex) abc\d{3,4} : abc111, abc9999

3) 한글자 그룹지정
[0-9] : 숫자 한글자
[a-z] : 영문(소문자) 한글자
[A-Z] : 영문(대문자) 한글자
[a-zA-Z] : 영문(대소구분없이) 한글자
[A-z] : 영문(대소구분없이) 한글자
[가-힣] : 한글 한글자

ex) a[0-9]+ : a 뒤에 숫자가 1회 이상 반복되는 경우
    a[0-9][0-9] : a 뒤에 숫자가 2회 반복되는 경우

4) 나머지 기호
^ : 시작
$ : 끝
| : 또는
. : 엔터를 제외한 모든 한글자
\ : escape character(이미 정의된 정규식기호를 일반기호화 하기 위해 사용)
    ex) http://www.naver.com http://www.google.com http://www.daum.net 
        => http://www\.[a-z]+\.[a-z]+

5) 기타
() : 연산 우선순위 표현, 서브그룹 지정
    ex) ^(a|b) : a 또는 b로 시작하는
        ^[ab]  : a 또는 b로 시작하는  
[^ab] : a, b를 제외한    
;

[ 정규식 표현 함수 ]
 정규식표현을 사용한 함수 

1. regexp_substr
- 정규식표현을 사용한 문자열 추출

 

** 문법
regexp_substr(대상,패턴[,검색위치][,발견횟수][,옵션][,서브그룹])

* 검색 위치 생략 시 1
* 발견횟수 생략 시 1(첫번째 패턴만 추출됨)
* 옵션 : c(대소구분), i(대소구분X), m(다중라인패턴선언)


예제)
select id,
       regexp_substr(id,'\d+'),     -- 숫자의 반복 추출
       regexp_substr(id,'[a-z]+')   -- 영어소문자의 반복 추출
  from professor;

select regexp_substr('abc1234 abc10','abc\d+'),       -- abc1234
       regexp_substr('abc1234 abc10','abc\d{2}'),     -- abc12
       regexp_substr('abc1234 abc10','abc\d{2}',1,2)  --abc10
  from dual;

select regexp_substr('abc abc10','abc\d*'),      -- abc
       regexp_substr('abc1234 abc10','abc\d*')   -- abc1234
  from dual;

예제)
create table regexp1(name varchar2(50));
insert into regexp1 values('abcd');
insert into regexp1 values('bcd');
insert into regexp1 values('cdb');
insert into regexp1 values('cd-a');
insert into regexp1 values('d100b');
commit;

select *
  from regexp1;
  
select regexp_substr(name, '^(a|b)[a-z]+')   -- abcd, bcd만 추출
  from regexp1;


예제) 이메일 아이디 추출
select email,
       substr(email,1,instr(email,'@')-1) as email_id1,
       regexp_substr(email,'[A-z0-9_-]+') as email_id2,
       regexp_substr(email,'(.+)@',1,1,null,1) as email_id3
  from professor;

예제) 전화번호에서 지역번호, 국번을 각각 추출
delete student where studno = 9999;
commit;

select tel,
       regexp_substr(tel, '\d+\)\d+-\d+') as 전화번호정규식1,
       regexp_substr(tel, '.+\).+-.+') as 전화번호정규식2,
       regexp_substr(tel, '\d+') as 지역번호,
       regexp_substr(tel, '\d+', 1, 2) as 국번,
       regexp_substr(tel, '(\d+)\)(\d+)-(\d+)',1,1,null,1) as 지역번호2,
       regexp_substr(tel, '(\d+)\)(\d+)-(\d+)',1,1,null,2) as 국번2
  from student;


예제) 문자열에서 일부 문자열 추출(학원명, 주소, 전화번호, 시작일, 종료일)
select regexp_substr('빅데이터와 머신러닝을 활용한 데이터 분석 전문가 양성과정  
                      국가기간전략훈련  우수과정 아이티윌 ( 서울 강남구 ☎ 02-6255-8002 ) 
                      훈련기관정보보기  훈련기간 : 2018-10-12 ~ 2019-03-27   훈련기관 직종별 취업률(전국)  TIP',
                      '([가-힝]+) \( ([가-힝 ]+) ☎ (.+) \).+',1,1,null,3) as value1     
 from dual;



select regexp_substr('아이티윌 ( 서울 강남구 ☎ 02-6255-8002 ) 훈련기관정보보기  훈련기간 : 2018-10-12 ~ 2019-03-27',
                     '(.+) \( (.+) ☎ (.+) \) .+ : (.+) ~ (.+)',1,1,null,5)   
 from dual;


[ 예상문제 ]
1. 다음 수행 결과로 적절한 것은? 2
SELECT
  REGEXP_SUBSTR('http://www.example.com/products',
                'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
  FROM DUAL;

1) http://www.example.com/products
2) http://www.example.com/ 
3) http://www.example.com
4) null

2. 다음 수행 결과로 적절한 것은? 2
SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR"
  FROM DUAL;
  
1) 500 Oracle Parkway, Redwood Shores, CA
2) , Redwood Shores,   
3) Redwood Shores
4) 500 Oracle Parkway,


2. 다음 수행 결과로 적절한 것은? 4
SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+',1,3)
  FROM DUAL;

1) 500
2) Oracle
3) Parkway
4) Parkway,



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

* 바꿀문자열 생략 시 문자열 삭제
* 검색위치 생략 시 1
* 발견횟수 생략 시 0(전체)
;

예) 이름 마스킹
select regexp_replace('김나나','나','X') 마스킹1,     -- 김XX
       regexp_replace('김나나','나','X',1,1) 마스킹2  -- 김X나
  from dual;


예제) PROFESSOR ID에서 숫자 삭제
select id, 
       regexp_replace(id,'\d'),           -- 모든 숫자 삭제(발견된 한자의 숫자를 모두 삭제하므로)
       regexp_replace(id,'[[:digit:]]'),  -- 모든 숫자 삭제(발견된 한자의 숫자를 모두 삭제하므로)
       regexp_replace(id,'[0-9]'),        -- 모든 숫자 삭제(발견된 한자의 숫자를 모두 삭제하므로)       
       regexp_replace(id,'\d{2,}')        -- 모든 두자 이상의 숫자 삭제
  from PROFESSOR;


예제) PROFESSOR ID에서 특수기호 삭제
select * from professor;
update PROFESSOR set id = 'captain!!' where id = 'captain';
update PROFESSOR set id = 'simson_1' where id = 'simson';
update PROFESSOR set id = '-lamb1' where id = 'lamb1';
commit;

select id, 
       regexp_replace(id, '[[:punct:]]') as result1,
       regexp_replace(id, '\W') as result2,          -- _를 제외한 특수기호 모두 삭제
       regexp_replace(id, '\W|_') as result3         
  from professor;

예제) PROFESSOR ID에서 only-u silver-her -lamb1 처럼 -을 포함하는 값을 전체 삭제(null로 리턴)
select id, 
       regexp_replace(id,'-'),
       regexp_replace(id,'[a-z0-9]*-[a-z0-9]+'),
       regexp_replace(id,'.*-.*')
  from professor;



예제) 다음 테이블 생성 후 상품명에 괄호 안에 있는 모든 글자를 괄호 포함 삭제
create table price
(
  상품명       varchar2(1000),
  조사일       varchar2(1000),
  판매가격      number,
  판매업소      varchar2(1000), 
  제조사       varchar2(1000),
  세일여부      varchar(5),
  원플러스원     varchar(5)
);


select distinct 상품명, 
       regexp_replace(상품명, '\(([A-z0-9가-힝 ]|[[:punct:]])+\)'),
       regexp_replace(상품명, '\(.+\)')
  from price;


예제) 아래 테이블 생성 수 oracle_alert_testdb.log 파일을 업로드
create table alertlog
(
  text varchar2(4000)
);

code     error
1109   signalled during: ALTER DATABASE CLOSE NORMAL...
00313  open failed for members of log group 1 of thread 1
....

select text,
       regexp_substr(text,'ORA-(\d+):? (.+)',1,1,null,1) as code,
       regexp_substr(text,'ORA-(\d+):? (.+)',1,1,null,2) as error
  from alertlog where text like '%ORA-%';





3. regexp_instr
4. regexp_like