본문 바로가기

별걸다하는 IT/데이터베이스 Database

[SQL] REGEXP_LIKE 사용법 알아보기 - LIKE와 IN을 합한 것과 같은 함수, 여러 패턴에 속하는 조건 추출하기, 정규식 & LIKE 쿼리

반응형

안녕하세요 ㅎㅎ 양햄찌 블로그 주인장입니다

오랜만에 데이터베이스 쿼리 관련 포스팅을 들고 왔어요.

■ REGEXP_LIKE 알아보기~

오늘 알아볼 함수는 REGEXP_LIKE 입니다.

regexp는 정규식이라는 뜻이죠! regexp_like는 단순히 문자열이 포함되어있는지 비교하는 LIKE를 넘어서, 정규식을 비교하여 일치할 경우 추출해주는 함수입니다.

해당 함수가 필요한 상황 예시

어떤 특정 문자열을 포함한 값을 추출하고 싶을 때, 우리 LIKE라는 조건을 이용했었죠?!

select *
from employees
where phone_number like '011%';
--> 폰 넘버가 011로 시작하는 행을 추출해라!

요렇게요! 그런데 만약 011로 시작하는거 외에도 555로 시작하는 녀석들도 뽑고 싶다면 어떻게 해야할까요?

select *
from employees
where phone_number like '011%'
or phone_number like '555%';

이렇게 되겠죠. 근데 조건이 이 두개 뿐만 아니라 011, 551, 552, 553 중에 하나로 시작하는 애들을 뽑고 싶으면?

select *
from employees
phone_number like in ('011%', '551%, '552%, '553%');

이렇게 쓰고 싶을 거예요. 그런데 이렇게 like와 in은 함께 사용할 수 없습니다. 에러 떠요. 대신 regexp_like 함수를 이용해 구현할 수 있어요.

select *
from employees
where regexp_like (phone_number, '^551|^552|^553|^011');

이렇게 하면 됩니당~~

 

위의 예제는, 자칫하면 길어질 수 있는 다중 LIKE 상황을 간단하게 줄여주는 예시인데요,

그 외에 로그인 아이디가 이메일형식인지 단순아이디인지 포맷 일치를 확인하고 싶다던가 이럴때도 사용할 수 있는게 REGEXP_LIKE입니당~~ 

 REGEXP_LIKE 문법 syntax~

이미지출처: https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm

 

단일 패턴 검색 

문법을 그림으로 표현해봤어요. 

매칭 파라미터 생략 가능 - 그럼 디폴트로 적용됨

매칭 파라미터는 대소문자 구별안한다 이런 옵션들을 주고 싶을 때 사용하는 건데, 특별히 지정할게 없다면 생략해도 됩니다. 

다중 패턴 검색

만약 위에 제시한 상황처럼 비교하고 싶은 패턴이 한 개가 아니라 여러 개일 수도 있겠죠? 그럴 경우 파이프( | )를 사용해 다중 검색을 할 수 있습니다.

 

▶ 리턴 값: 

총 조건에 일치하는 행의 개수를 리턴합니다.

 REGEXP_LIKE 매칭 파라미터

지정할 수 있는 옵션들이 머머 있는지 살펴봅시다.

match parameter 의미
i 대소문자 구별하지 않고 매칭
c 대소문자 구별해서 매칭 
n allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, the period does not match the newline character.
원래 점('.')이 와일드카드에서는 '하나의 문자와 대응'이란 뜻인데, 이걸 마침표의 역할로 하게 하겠다는거 즉 개행 문자와 일치시킨다. 점은 디폴트에서 개행문자가 아니라 와일드카드에서 가지는 의미로 사용된다.
m 멀티플라인 모드 파라미터. '대상 문자열(컬럼명)'이 한 줄이 아니라 여러 줄을 가질때 사용할 수 있는 옵션입니다. m파라미터를 가지면 오라클은 '^'가 나오면 시작점으로 $를 끝점으로 생각하고 수행합니다.
x whitespace 문자가 무시됩니다. (본래 디폴트는 무시 안함) 

참고) 화이트스페이스 문자는 공백 뿐만 아니라, 탭 개행, 캐리지리턴 등 비스무리한 것들을 모두 포괄하는 걸 말해요.

EXAMPLES 예시들~

▷ 특정 문자열이 들어있는 데이터를 추출할 때!

select * from employees
where regexp_like(first_name, 'ne');

employees테이블의 first_name 컬럼에서 'ne'라는 문자열을 포함하고 있는 데이터들을 출력해라! 라는 뜻이 되겠죠?

결과 총 4개 

 특정 문자열이 들어 있는 데이터를 추출하는데 대소문자 구분하지 않고 검색하고 싶을 때!

select * from employees
where regexp_like(first_name, 'ne', 'i');

i는 대소문자를 구분하지 않을 때 사용합니다. employees테이블의 first_name 컬럼에서 대소문자 관계 없이 'ne'라는 문자열을 포함하고 있는 데이터들을 출력해라! 

매치 파라미터 i덕분에 'Neena' 대문자N과 소문자 e의 조합도 결과에 포함되었어요.

 

▷ 두 개의 문자열 중 하나를 포함하고 있는 행 추출하기 

select * from employees
where regexp_like(first_name, 'ett|ana');

파이프( | )를 사용하면 다중 표현을 할 수 있어요. employees테이블의 first_name 컬럼값들 중 'ett'라는 문자열이 들어가 있거나 'ana'라는 문자열이 들어가 있는 행 검색하기 

 

▷정규식 패턴 안에 파이프를 사용해서 여러 조건 검색하기 

select * from employees
where regexp_like(first_name, 'an(c|i|e)');

employees테이블의 first_name 컬럼에서 anc 또는 ani 또는 ane 가 포함된 결과값들을 찾아서 보여줘!

▷대소문자 관계 없이 특정 문자열로 시작하거나 끝나는 결과만 뽑기 

select * from employees
where regexp_like(first_name, '^na|na$', 'i');

'^'는 시작을 의미하고 '$'는 끝을 의미합니다. employees테이블의 first_name 컬럼에서 대소문자 관계 없이 na로 시작하거나 끝나는 행들만 찾습니다.

▷정규식 포맷에 일치하는 레코드들 추출하기

select * from employees
where regexp_like(phone_number, '^51[0-9]\.[0-9]{3}\.[0-9]{4}$');

이번에는 이메일형식, 전화번호부 형식, 주민번호 형식 등, 정규식으로 000.000.0000 형식을 가지고 있는 값들만 뽑아볼거예요. 그런데 이 중 전화번호가 '51'로 시작하는 레코드만 뽑는거~ ex) 510.423.4555 

정규식에서 [0-9]는 0에서 9까지의 값들 중 하나를 의미하죠. {}는 반복 개수입니다. 

더 많은 정규표현식을 알면 다양하게 검색할 수 있겠죠?? 정규표현식 활용법을 알고 싶으신 분은 아래 포스팅을 참조해주세요! 링크: https://jhnyang.tistory.com/127

 

[리눅스 유닉스] 정규표현식 (Regular Expression) 메타문자 정리. vi편집기에서 정규표현식 응용해 검�

[리눅스 / 유닉스 목차] 안녕하세요! 지난 포스팅들에서는 정말 기본적인 vi 단축키에 대해서 알아봤는데요. 이번에는 알아두면 정말정말 유용한 vi 에디터 사용법 2편으로 찾아왔습니다. 검색에�

jhnyang.tistory.com

▷regexp_like에 NOT 붙여서 반대케이스만 뽑기

select * from employees
where not regexp_like(phone_number, '^515|^555);

이렇게 하면 515랑 555로 시작되지 않는 경우만 뽑아주는거

 REGEXP_LIKE 이외에 더 알아두면 좋을 정규식 함수들

함수명  설명
REGEXP_REPLACE  정규식 패턴을 검색하여 대체 문자열로 변경합니다.
REGEXP_INSTR 정규식 패턴에 대해 문자열을 검색하고 일치가 발견된 위치를 반환합니다.
REGEXP_SUBSTR 지정된 문자열 내에서 정규식 패턴을 검색하고 일치하는 부분 문자열을 추출합니다. 
REGEXP_COUNT  입력 문자열에서 패턴 일치가 발견되는 횟수를 반환합니다.

오늘도 수고하셨어요. 도움이 되셨다면 지나치기전 공감은 어떤가요?!

지식 전파에 큰 힘이 됩니다. 다음에 또 뵈어요~

반응형