[SQL] 18. 정규식 함수

18. 정규식 함수

오늘은 정규식 함수에 대해 알아보자.
정규식 함수는 표준 구문 규칙을 사용하여 문자열 데이터의 간단한 패턴 및
복잡한 패턴을 검색하고 조작할 수 있으며,
특히 데이터 정제 시 자연어 처리에 큰 효율을 발휘하는 함수이다.

또한, 정규식 함수의 메타문자는 SQL을 넘어 R, Python 등 많은 언어에서 유사하게 사용되고 있어,
정성적 빅데이터를 다루는 사람, 분석가라면 필히 알아두어야 하겠다.

ORACLE SQL에서 많이 쓰이는 정규식 함수로는 아래의 5가지가 있다.

메타 문자

정규식 함수는 메타 문자와 함께 사용되는데, 메타 문자란 일련의 문자와 같이
특별한 의미를 지닌 특수문자이다.

이를 활용해 더욱 간단하게 문자형 데이터를 검색/조작할 수 있다.
메타문자를 정리한 표를 참고하고, 실제 사용하면서 활용방법을 확인하자.

regexp_like

regexp_like는 like와 일치하는 함수로, regexp_like(변수, 조건식) 으로 사용한다.
예제) 이름에 IN과 EN이 들어간 사원의 이름을 출력하시오!
select ename
from emp
where ename like ('%EN%') or ename like ('%IN%');

이제 regexp_like를 사용하여 검색해보자.
select ename
from emp
where regexp_like (ename, '(EN|IN)' );

하드코딩이 될 수 있는 query를 간단하게 줄여준다.

regexp_replace

regexp_replace 역시 replace와 유사한 함수로, 특정 조건의 문자를 원하는 문자로 변경할 수 있다.

사용법은 regexp_replace(변수, 조건식, 변경할 문자) 이다.

예제) 이름(ename)과 월급(sal)을 출력하는데 월급의 숫자에서 0~2는 ★로 표시하시오!
select ename, sal, regexp_replace(sal, '[0-2]+', '★')
from emp;

위 정규식에서 [0-2]뒤에 +를 추가해주면서 맨 위 KING의 월급인 5000이
5★★★가 아닌 5★로 출력되었다. 사실상 더 보안을 지키기 좋은 형태로 변경되었다.

regexp_instr

regexp_instr은 특정 조건 단어의 철자의 위치를 출력하는 함수로,
regexp_instr(변수, 조건식, 찾은 단어 중 몇 번째(n), 앞의 n에서 몇 번째 )

쉽게 예제를 통해 알아보자.

예제) 입사일(hiredate)에서 '/'라는 문자가 몇 번째에 있는지 확인하시오!
① 우선 hiredate는 date형태의 데이터이므로 문자형태로 변경해줘야 한다.
select hiredate, to_char(hiredate) from emp;

→ 특별히 다른 변경 조건을 주지 않아 1981/11/17이 81/11/17의 형태로 변경되었다.

여기서 정규식을 이용해 '/'의 위치를 확인하려면

select hiredate, to_char(hiredate),
regexp_instr(to_char(hiredate), '[/]', 1, 1) as 첫번째,
regexp_instr(to_char(hiredate), '[/]', 1, 2) as 두번째 from emp;

이러한 방법으로 데이터 정제 시 특정 조건의 문자열 위치를 확인할 수 있다.

regexp_substr

regexp_substr는 regexp_substr(변수, 조건식,
조건에 해당하는 시작위치, 조건에 해당하는 끝 위치) 이다.

쉽게 이해하려면 위의 regexp_instr의 설명을 참고하라.

substr과 같이 특정 조건에 해당되는 문자열만 뽑아내는 기능을 가지고 있으며,
공백 단위로 문자열 데이터를 나누어 사용하기에 좋다.

예제) 입사일(hiredate)를 출력하는데 년도, 월, 일로 구분하여 각각 출력하시오!
select hiredate,
regexp_substr(to_char(hiredate), '[^/]+', 1, 1) as 년도,
regexp_substr(hiredate, '[^/]+', 1, 2) as 월,
regexp_substr(hiredate, '[^/]+', 1, 3) as 일
from emp;

regexp_count

특정 조건의 문자열이 몇 번 나왔는지를 확인하는 함수로,
regexp_count(대상 데이터, 조건)으로 사용한다.

예를 들어 'abcde'를 10번 반복한 'abcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcde'에서
'ea'라는 문자는 몇 번 나왔는지 확인해보자.

select regexp_count('abcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcdeabcde', '(ea)') as 확인
from dual;

나중에 '어떤 책 또는 문장에서 사용된 단어를 확인해보자'와 같은 업무 진행시에는
대소문자 구분, 따옴표(.) 분리 등 다양한 추가 조건을 기입해줘야 깔끔한 결과를 확인한다.

Tips

특정 테이블에 있는 데이터 타입 확인하는 방법
select * from ALL_TAB_COLUMNS
where TABLE_NAME='EMP';

=================이 글은 Markdown을 활용해 작성하였습니다=================