정규표현식
정규식으로 문자열을 검색, 추출, 계수, 치환하는 REGEXP 함수와 문자 클래스 및 부정 조건
#핵심 요약
정규식 지원은 데이터베이스가 그대로 찾기(LIKE)를 넘어, 규칙(패턴)을 가진 문자열을 검색하거나 조작하는 기능을 내장한다는 뜻임
데이터 검증을 클라이언트가 아니라 데이터베이스에서 할 수 있음
업무 예: 이메일 형식이 맞는 고객, 010으로 시작하는 전화번호, A로 시작하고 숫자 4개가 붙은 상품코드
함수 다섯 가지: REGEXP_LIKE(조건), REGEXP_SUBSTR(잘라내기), REGEXP_COUNT(개수), REGEXP_INSTR(위치), REGEXP_REPLACE(바꾸기)
#개념과 동작 원리
수업 자료에 나온 패턴 기호:
| 기호 | 뜻 |
|---|---|
^ |
앞글자(문자열 시작) |
$ |
끝글자(문자열 끝) |
( ) |
묶은 것을 문자열로 인식 `(EN |
[ ] |
안의 문자를 하나하나 인식[0-3]은 0~3 중 한 글자 |
[^0-9] |
숫자 0~9가 아닌 글자 |
[^ ]+ |
공백이 아닌 글자가 1개 이상 이어진 덩어리(공백으로 나뉜 단어 하나) |
[[:alpha:]], [[:digit:]] |
알파벳, 숫자 |
\1, \2 |
치환 문자열에서 첫째, 둘째 괄호 그룹이 맞은 부분 |
패턴, 검색 위치, 일치 순번은 서로 다른 인수이며 함수마다 미일치 반환값이 다름
#주요 용어와 구성 요소
| 구문 | 의미 |
|---|---|
| 패턴 | 찾을 문자 집합과 반복, 위치 조건을 나타내는 식임 |
| 캡처 그룹 | 괄호로 묶어 일치한 일부를 참조할 수 있게 하는 단위임 |
| 발생 순번 | 검색 시작 위치 이후 몇 번째 일치를 사용할지 지정하는 값임 |
#필수 명령어 또는 SQL
| 항목 | 실행 조건 |
|---|---|
| 작업 대상 | emp, locations, winter7의 문자열, 급여 마스킹 표현식 |
| 필요 권한 | 대상 테이블의 조회 권한이 있는 계정 |
| 바꿀 입력값 | 검색 대상 열, 패턴, 일치 순번, <EMAIL>은 시험용 자리표시 |
| 결과 해석 | 일치 문자열, 위치, 개수, 치환문을 구분하며 과제의 실제 출력, 건수는 기재 없음 |
#REGEXP_LIKE
SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename, '(EN|IN)');결과 해석: 이름에 EN이나 IN이 들어간 사원임(조건식)
#REGEXP_SUBSTR
SELECT street_address,
REGEXP_SUBSTR(street_address, '[^ ]+', 1, 1),
REGEXP_SUBSTR(street_address, '[^ ]+', 1, 2),
REGEXP_SUBSTR(street_address, '[^ ]+', 1, 3)
FROM locations;결과 해석:
설명: 인수는 (컬럼, 패턴, 시작 위치, 몇 번째 매치)임
시작 위치는 보통 1, 마지막 숫자가 몇 번째 단어인지임'1297 Via Cola di Rie'에서 1번째는 1297, 2번째는 Via, 3번째는 Cola임
단어가 두 개뿐인 주소에서 3번째를 요청하면 NULL이 나옴
#REGEXP_COUNT
SELECT REGEXP_COUNT('ccacctttccctccactcctcacgttctcacc', 'gtc') AS count
FROM dual;
SELECT REGEXP_COUNT(LOWER(win_text), '(elsa)')
FROM winter7;결과 해석:
실습에서는 긴 DNA 염기 서열 문자열에서 gtc가 몇 번 나오는지 셌음
두 번째 문장은 영화 대본을 한 줄씩 넣은 테이블(win_text VARCHAR2(4000))에서 등장인물 이름의 등장 횟수를 세는 실습 과제임
#REGEXP_INSTR
SELECT street_address, REGEXP_INSTR(street_address, '[[:alpha:]]')
FROM locations;결과 해석: 주소에서 처음 알파벳이 나오는 자리 번호임
#REGEXP_REPLACE
SELECT ename, REGEXP_REPLACE(sal, '[0-3]', '*')
FROM emp;
SELECT REGEXP_REPLACE('<EMAIL>', '(^.).*(@.*$)', '\1***\2')
FROM dual;결과 해석:
설명: 첫 문장은 월급의 숫자 0~3을 모두 *로 바꿈
주민등록번호 같은 개인정보를 가릴 때 씀
두 번째 문장의 패턴은 그룹1 (^.)이 맨 앞 한 글자, 그룹 밖 .*이 중간 글자, 그룹2 (@.*$)이 @부터 끝임
치환 문자열 \1***\2는 첫 글자와 @ 이후를 남기고 가운데를 ***로 바꿈
SELECT ename, REGEXP_REPLACE(sal::text, '[0-3]', '*', 'g')
FROM emp;결과 해석:
설명: PostgreSQL에서는 숫자를 ::text로 바꾸고, 모두 바꾸려면 'g' 옵션을 줌
#구문과 옵션 설명
| 구문 | 의미 |
|---|---|
| position / occurrence | 검색 시작 문자 위치와 일치 순번이며 SUBSTR, INSTR의 기본값은 각각 1임 |
| REGEXP_INSTR return_option | 0은 일치 시작 위치, 1은 일치 다음 위치이며 미일치면 0임 |
| REGEXP_REPLACE occurrence | 0이면 모두 치환하며 양수이면 지정한 일치만 치환함 |
| match_param i / c | 대소문자 무시 또는 대소문자, 악센트 구분 지정임 |
| match_param n / m / x | 점의 줄바꿈 일치, 줄 단위 앵커, 패턴 공백 무시 옵션임 |
| PostgreSQL g | regexp_replace에서 모든 일치를 치환하는 플래그임 |
#사용 조건과 주의사항
숫자와 문자열의 암시적 변환은 형식, NLS 설정의 영향을 받을 수 있으므로 패턴 대상의 타입을 먼저 확인함
REGEXP 함수에 지정하는 패턴 크기 한도는 512바이트임