[ORACLE] 향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 2

in Study on Oracle
[ORACLE] 향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 2

향상된 데이터 조회를 위한 정규표현식과 계층화 쿼리 2

정규 표현식(Regular Expression)

  • 정규 표현식
    • 주어진 문장 패턴과 매치시킬 수 있는 어떤 표현을 기술할 수 있는 기호와 원소들의 집합으로 매우 강력하고 실용적인 패턴 매치 기법
    • POSIX 표준 문법을 따름
    • 다양한 매치와 검색을 정의하는 메타기호(메타캐릭터)들의 조합으로 구성
    • 대소문자를 구분

When?

  • ETL (Extract, Transform, Load)
  • Data Mining(누적된 정보를 통한 인과관계/통계 분석)
  • Data Cleansing(정제)
  • Data Validation(검증)
  • 제약조건을 이용한 테이블 Data 유효성 검증

What?

  • 중복 단어의 확인
  • 특별한 상태에서 공백의 제거
  • 문자의 파싱
  • Text에서 전화번호, 우편번호, 이메일 주소, 주민등록번호, IP주소, 파일이름, 경로명 등을 검증 및 추출
  • HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능

정규표현식 함수

함수 명함수 설명
REGEXP_LIKELike 연산과 유사하며 정규식 패턴을 검색
REGEXP_REPLACE정규식 패턴을 검색하여 대체 문자열로 변경
REGEXP_INSTR정규식 패턴을 검색하여 위치 반환
REGEXP_SUBSTR정규식 패턴을 검색하여 부분 문자 추출
REGEXP_COUNT정규식 패턴을 검색하여 발견된 횟수 반환

REGEXP_INSTR 과 REGEXP_SUBSTR 을 조합하면 특정 위치의 데이터를 뽑을 수도 있다.

정규표현식 메타 캐릭터

메타 캐릭터기능
^문자열의 시작 부분과 일치
$문자열의 끝 부분과 일치
*0개 이상(zero or more) 일치
+하나 이상(one or more) 일치
?0 또는 1개 일치(zero or one)
.NULL을 제외한 모든 문자와 일치
|OR
[ ][ ]에 있는 문자 중 하나를 일치시키려는 목록을 지정
[^ ][^ ]에 있는 문자를 제외한 모든 문자를 일치시키려는 리스트 지정
( )표현식을 하위 표현식으로 그룹화하는데 사용
{m}m번 일치
{m, }적어도 m번 일치
{m, n}적어도 m번 일치 ~ n번 이하 일치
\n()번째 하위 표현식과 일치
[..]하나 이상의 문자가 될 수 있는 하나의 집합요소와 일치
[::]문자 클래스와 일치. [:digit:], [:punct:], [:alpha:] 등
[==]equivalence 클래스와 일치
메타 캐릭터기능
\d숫자와 일치
\D숫자가 아닌 것과 일치
\w영문자 숫자 밑줄문자(_)와 일치
\W영문자 숫자 밑줄문자(_) 이외의 문자와 일치
\s공백문자와 일치
\S공백이 아닌 문자와 일치
\A문자열의 시작 부분 일치, 새 행의 문자 앞에 있는 문자열의 끝 부분과 일치
\Z문자열의 끝과 일치
*?앞선 패턴이 0번 이상 발생
+?앞선 패턴이 한 번 이상 발생
??앞선 패턴이 0번 또는 1번 발생
{n}?앞선 패턴이 n번 일치
{n, }?앞선 패턴이 적어도 n번 일치
{n,m}?앞선 패턴이 적어도 n번 ~ m번 이하 일치

오라클 공식 Docs, Ref를 보고 확인, 활용하도록 하자.

정규표현식 함수 : REGEXP_LIKE()

  • like 연산자의 정규 표현식 적용 함수
REGEXP_LIKE(해당문자열, 정규식패턴 [, 매칭매개변수옵션])

-- string 문자열
-- 검색될 문자열
-- pattern 정규식 패턴
-- 비교할 정규식 패턴을 나타내는 리터럴 문자열
  • i : 대소문자 무관하게 처리
  • c : 대소문자 구분 (기본값)
  • n : 마침표(.) 문자가 새 라인과 일차하도록 허용
  • m : 소스 문자열을 앵커 문자(^$)를 활성화하는 여러 줄로 처리
  • x : 공백 문자 무시

예시를 보고 익혀보도록 하자.

  • 사원명에서 “n”으로 끝나는 모든 사원명 출력

      SELECT. last_name
    FROM. employees
   WHERE. REGEXP_LIKE(last_name, '(*)n$'); -- n으로 끝나는 모든 글자를 반환
     LAST_NAME
    1De Haan
    2Hartstein
    3Whalen

  • 업무에서 “S”로 시작하는 모든 업무 출력

      SELECT. job_id
    FROM. employees
   WHERE. REGEXP_LIKE(job_id, '^S(*)');
     JOB_ID
    1SA_MAN
    2SA_REP
    3SA_REP
    4SA_REP
    5ST_CLERK
    6ST_CLERK
    7ST_CLERK
    8ST_CLERK
    9ST_MAN

  • 이름이 “Ste”로 시작하는데 “v”나 “ph”를 포함하며 “en”으로 끝나는 모든 이름과 성을 출력

      SELECT. first_name, last_name
    FROM. hr.employees
   WHERE. REGEXP_LIKE(first_name, '^Ste(v|ph)en$');
     FIRST_NAMELAST_NAME
    1StevenKing
    2StevenMarkle
    3StephenStiles

    EMPLOYEES 에 대한 이 query에서는 first_name 에 Steven 또는 Stephen이 포함된 모든 사원 정보를 표시한다.

    • ^ 는 표현식의 시작
    • $ 는 표현식의 끝
    • | 는 둘 중 하나(OR)

정규표현식 함수 : REGEXP_INSTR()

  • 지정된 정규식 패턴을 문자열에서 검색하고 일치 항목이 있는 문자열의 위치(숫자값)를 반환하고, 미일치시 0을 반환
  • 처음 문자의 위치는 1
REGEXP_INSTR(해당문자열, 정규식패턴 [, 시작위치 
             [, 일치횟수 [, 반환할 문자위치 [, 매칭 파라미터]]]])

마찬가지로 예시를 통해 이해해보자.

SELECT. street_address,
        REGEXP_INSTR(street_address, '[^[:alpha:]]')
  FROM. hr.locations
 WHERE. REGEXP_INSTR(street_address, '[^[:alpha:]]') > 1;
 STREET_ADDRESSREGEXP_INSTR(STREET_ADDRESS.’[^[:ALPHA:]]’)
1Magdalen Centre, The Oxford Science Park9
2Schwanthalerstr. 703116
3Rua Frei Caneca 13604
4Murtenstrasse 92114
………

REGEXP_INSTR 함수로 주소를 검색하여 문자가 아닌(^) 첫 번째 알파벳 문자의 위치를 찾는다.

[:alpha:] 는 임의의 알파벳 문자와 일치한다.

  • [ 는 표현식을 시작
  • ^ 는 부정 의미
  • [:alpha:] 는 알파벳 문자 클래스 표시
  • ] 는 표현식을 종료

정규표현식 함수 : REGEXP_SUBSTR()

REGEXP_SUBSTR(COLUMN, [REG_EXP], [START_INDEX], [GROUP_INDEX])
  • COLUMN : 칼럼명
  • REG_EXP : 정규표현식 작성
  • START_INDEX : 해당 정규표현식을 검색할 문자열의 INDEX 지정
  • GROUP_INDEX : 해당 정규표현식으로 잘라진 그룹이 2개 이상이라면 INDEX 지정해 그룹 선택
SELECT  last_name,
        REGEXP_SUBSTR(last_name, 'a|e|i|o|u', 1, 1, 'i')
        AS "첫 모음"
  FROM. employees;
 LAST_NAME첫 모음
1AbelA
2Daviesa
3De Haane
4ErnstE
5Faya
6Gietzi
………

last_name 에서 첫 모음을 대소문자 무관하게 출력한다.

i 를 옵션으로 지정했기 때문에 대소문자에 무관하게 값이 출력된다.

SELECT. street_address,
        REGEXP_SUBSTR(street_address, ' [^ ]+ ') AS "Road"
  FROM. locations;
 STREET_ADDRESSRoad
12014 Jabberwocky RdJabberwocky
22011 Interiors BlvdInteriors
32004 Charade RdCharade
………

LOCATIONS 테이블에서 road name 추출.

이를 위해 REGEXP_SUBSTR 함수를 사용하여 STREET_ADDRESS 열에서 첫 번째 공백 뒤에 있는 내용을 반환.

  • [ 는 표현식을 시작
  • ^ 는 NOT을 나타냄
  • 공백을 나타냄
  • ] 는 표현식을 종료
  • + 는 1 이상을 나타냄
  • 공백을 나타냄
SELECT. street_address,
        REGEXP_INSTR(street_address, '[^ ]+', 1, 3) AS REGEXP_INSTR,
        REGEXP_SUBSTR(street_address, '[^ ]+' 1, 3) AS REGEXP_SUBSTR
  FROM. locations
 WHERE. REGEXP_INSTR(street_address, '[^ ]+', 1, 3) > 1;
  • 공백이 아닌 세 번째 단어를 검색하기 위해 1~3까지의 단어 중 세번째에 해당하는 단어를 출력한다. (단어 2개의 행은 제외)
 STREET_ADDRESSREGEXP_INSTRREGEXP_SUBSTR
12014 Jabberwocky Rd18Rd
22011 interiors Blvd16Blvd
32004 Charade Rd14Rd

정규표현식 메타 캐릭터 : REGEXP_REPLACE()

REGEXP_REPLACE(해당문자열, 정규식패턴, [, 대체문자열 [, 시작위치 
	[, 일치횟수 [, 매칭매개변수]]]])
  • replacement_string : 대체문자열, 해당 문자여로가 패턴이 일치할 때 대체될 문자. 생략할 시 패턴에 일치하는 문자가 제거됨
  • Start_position : 시작위치, 검색 시작 위치를 지정. 기본값 = 1.

  • nth_appearance : 일치횟수, n번째 일치된 문자열 대체. 기본값 = 0. 모든 값 대체.

  • “Oracle Database” 에서 a, e, i, o, u 를 첫 번째에서 찾아 ‘Z’로 변경
SELECT. REGEXP_REPLACE('Oracle Database', 'a|e|i|o|u', 'Z', 1, 0, 'i')
    AS. REP
  FROM. dual;
 REP
1ZrZclZ DZtZbZsZ
  • 대상 열에서 문자열에 포함된 숫자를 모두 ‘*’ 로 변경
SELECT. street_address, REGEXP_REPLACE(street_address, '\d', '*')
    AS. "Road"
  FROM. locations;
 STREET_ADDRESSRoad
12014 Jabberwocky Rd** Jabberwocky Rd
22011 Interiors Blvd** Interiors Blvd
32004 Charade Rd** Charade Rd
  • 전화번호에서 ‘.’ 을 ‘-’로 변경
SELECT. last_name, REGEXP_REPLACE(phone_number, '\.', '-')
    AS. phone
  FROM. employees;

| | LAST_NAME | PHONE_NUMBER | | — | — | — | | 1 | OConnell | 650-507-9833 | | 2 | Grant | 650-507-9844 | | 3 | Whalen | 515-123-4444 | | 4 | Hartstein | 515-123-5555 |

  • 마침표(.) 구분자를 대시(-) 구분자로 대체하도록 전화번호 형식을 다시 지정할 수 있다.
  • phone_number는 소스 열
  • \. 는 검색 패턴
  • 작은 따옴표(’ ‘)를 사용하여 리터럴 문자 마침표(.)를 검색
  • 백슬래시(\)를 사용하여 일반적으로 메타 문자로 처리되는 문자를 검색
  • - 는 대체 문자열

정규표현식 메타 캐릭터 : REGEXP_COUNT()

  • 문자열 중에 매칭되는 문자열 또는 패턴이 몇 번 나오는지 횟수 출력
REGEXP_COUNT(source_char, pattern [, position [, occurrence [, match_option]]])
  • 대상 문자열에서 ‘a’ 글자는 몇 번 반복될까 ?
SELECT. REGEXP_COUNT('Oracle Database', 'a') AS REP
  FROM. dual;
  • 지정한 전화번호 패턴과 일치하면 1, 아니면 0 출력
SELECT. phone_number,
        REGEXP_COUNT(phone_number,
        '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})')
    AS. phone_format
  FROM. employees;
 PHONE_NUMBERPHONE_FORMAT
1650.121.80091
2650.121.29941
3650.121.28741
4011.44.1344.4290180

정규표현식 활용 1

  • Email 주소 분리 = IDDomain
  • REGEXP_SUBSTR(소스 문자열, Pattern [, 위치 [, 발생 횟수 [, Match를 시도할 때의 옵션]]])
  • @를 기준으로 @가 아닌 앞 글자와 뒷글자 출력
SELECT. REGEXP_SUBSTR(email, '[^@]+', 1, 1) AS "ID",
        REGEXP_SUBSTR(email, '[^@]+', 1, 2) AS "MailAddr"
  FROM. (SELECT '&input_email' email FROM dual);

[대체 변수 입력] input_email에 대한 값 입력 ::

대체 변수로 사용자가 이메일을 입력할 수 있다.

첫 번째 정규표현식에 의해 @ 를 기준으로 앞글자를 출력하고,

두 번째 정규표현식에 의해 @ 를 기준으로 뒷글자를 출력한다.

 IDMailAddr
1heoj10272gmail.com

정규표현식 활용 2

  • 주민번호 Masking
  • REGEXP_REPLACE(소스 문자열, Pattern [, 바꿀 문자열 [, 위치 [, 발생횟수 [Match 파라미터]]]])
SELECT. REGEXP_REPLACE(SSN, '[0-9]', '*', 7) REG_REP
  FROM. (SELECT '&jaeyoung' AS SSN FROM dual);

[대체 변수 입력] jaeyoung에 대한 값 입력 ::

내 주민번호를 입력하면, 마스킹되어 저장된다.

들어온 문자열에서 7번째 까지만 숫자로 출력하고, 나머지는 * 로 바꾼다.

정규표현식 활용 3

  • 전화번호 패턴 만들어 저장하기
SELECT. REGEXP_REPLACE('010.1234.1234', '([[:digit:]]{3})\.([[:digit:\\{4})\.
        ([[:digit:]]{4})','(\1) \2 - \3')
    AS. "Result1"
  FROM. dual;
 Result1
1(010) 1234 - 1234
  • 생년월일 패턴 만들어 저장하기
SELECT. REGEXP_REPLACE('&생년월일_YYYYMMDD',
        '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})',
        '\1 년 \2 월 \3 일')
  FROM. dual;
 REGEXP_REPLACE(’20050819’, ‘([[:DIGIT:]]{4})…
12005 년 08 월 19 일

© 2022. All rights reserved.