[Oracle] 정규표현식(REGEXP) 자주 사용하는 패턴 4가지

정규표현식을 사용할 수 있는 REGEXP_함수를 익혔다면, 
실제로 어떤 패턴을 자주 쓰는지도 알아두면 좋다.
이번 글에서는 실무에서 자주 쓰이는 4가지 정규표현식 패턴을 
예제와 함께 간단히 정리해봤다.

 

 

1) REGEXP_COUNT

1-1) 문법

REGEXP_COUNT(source_string, pattern [, position [, match_parameter]])

 

1-2) 특징

• 정규표현식 패턴이 문자열 내에 몇 번 등장하는지 세는 함수
• \d, \s, [a-z] 등 정규식 패턴을 사용 가능
• match_parameter에 'i'를 넣으면 대소문자 무시

 

1-3) 예시

SELECT
  REGEXP_COUNT('banana', 'a') AS cnt_a,
  REGEXP_COUNT('abc123xyz456', '\d') AS cnt_digits,
  REGEXP_COUNT('hello world this is oracle', '\s') AS cnt_spaces,
  REGEXP_COUNT('ApPleBanAnA', '[aA]') AS cnt_aA
FROM dual;

• cnt_a: 'a'가 3번 등장
• cnt_digits: 숫자가 6번 등장
• cnt_spaces: 공백이 4번 등장
• cnt_aA: 대소문자 'a' 총 4번 등장

결과값

 

2) REGEXP_SUBSTR

2-1) 문법

REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, return_option [, match_parameter]]]])

 

2-2) 특징

• 정규표현식에 매칭되는 문자열을 추출
• occurrence로 두 번째, 세 번째 매칭도 추출 가능
• 이메일, 숫자, 영문 단어 등 다양한 추출에 유용

 

2-3) 예시

SELECT
  REGEXP_SUBSTR('abc123xyz456', '\d+') AS first_number,
  REGEXP_SUBSTR('abc123xyz456', '\d+', 1, 2) AS second_number,
  REGEXP_SUBSTR('test@email.com', '@\w+\.\w+') AS domain,
  REGEXP_SUBSTR('123 start here', '[a-zA-Z]+') AS first_word
FROM dual;

• first_number: 123
• second_number: 456
• domain: @email.com
• first_word: start

결과값

 

3) REGEXP_REPLACE

3-1) 문법

REGEXP_REPLACE(source_string, pattern, replace_string [, position [, occurrence [, match_parameter]]])

 

3-2) 특징

• 정규표현식 패턴에 해당하는 부분을 치환
• \1, \2로 그룹 지정 가능
• 숫자 제거, 마스킹, 특수문자 제거 등에 활용

 

3-3) 예시

SELECT
  REGEXP_REPLACE('abc123xyz456', '\d', '') AS remove_digits,
  REGEXP_REPLACE('hello world oracle', '\s', '-') AS replace_space,
  REGEXP_REPLACE('myemail@domain.com', '(.{2}).+(@)', '\1****\2') AS masked_email,
  REGEXP_REPLACE('Hello! This, is: SQL?', '[^a-zA-Z0-9\s]', '') AS clean_text
FROM dual;

• remove_digits: abcxyz
• replace_space: hello-world-oracle
• masked_email: my****@domain.com
• clean_text: Hello This is SQL

결과값

 

4) REGEXP_LIKE

4-1) 문법

REGEXP_LIKE(source_string, pattern [, match_parameter])

 

4-2) 특징

• 정규표현식과 일치 여부를 TRUE/FALSE로 반환
• LIKE보다 정교한 조건 검색이 가능
• 대소문자 구분 없이 검색 가능 ('i' 옵션)

 

4-3) 예시

SELECT
  CASE WHEN REGEXP_LIKE('abc123', '^\w+$') THEN 'Y' ELSE 'N' END AS is_word,
  CASE WHEN REGEXP_LIKE('123-456-7890', '^\d{3}-\d{3}-\d{4}$') THEN 'Y' ELSE 'N' END AS is_phone,
  CASE WHEN REGEXP_LIKE('test@email.com', '^\w+@\w+\.\w+$') THEN 'Y' ELSE 'N' END AS is_email,
  CASE WHEN REGEXP_LIKE('ABC', 'abc', 'i') THEN 'Y' ELSE 'N' END AS match_ignore_case
FROM dual;

• is_word: 'Y'
• is_phone: 'Y'
• is_email: 'Y'
• match_ignore_case: 'Y'

결과값

 

5) 실무에서 자주 쓰는 정규표현식 패턴 4가지

5-1) 숫자만 포함된 문자열

SELECT
  CASE WHEN REGEXP_LIKE('123456', '^\d+$') THEN 'Y' ELSE 'N' END AS only_digits
FROM dual;

• only_digits: 'Y'

 

5-2) 이메일 형식

SELECT
  CASE WHEN REGEXP_LIKE('user@mail.com', '^\w+@\w+\.\w+$') THEN 'Y' ELSE 'N' END AS is_email
FROM dual;

• is_email: 'Y'

 

5-3) 영어 대문자만

SELECT
  CASE WHEN REGEXP_LIKE('HR', '^[A-Z]+$') THEN 'Y' ELSE 'N' END AS is_uppercase
FROM dual;

• is_uppercase: 'Y'

 

5-4) 특수문자 포함 여부

SELECT
  CASE WHEN REGEXP_LIKE('Hello!', '[^a-zA-Z0-9\s]') THEN 'Y' ELSE 'N' END AS has_special
FROM dual;

• has_special: 'Y'

 

6) REGEXP_LIKE 사용 시 주의사항

Oracle에서는 REGEXP_LIKE 함수가 논리값을 반환하기 때문에,
SELECT절에서 단독으로 사용할 경우 오류가 발생한다.

-- ❌ 아래처럼 쓰면 ORA-00904 오류 발생
SELECT REGEXP_LIKE('abc123', '^\w+$') AS result
FROM dual;

대신 아래처럼 CASE WHEN으로 감싸서 사용해야 한다.

 

SELECT
  CASE WHEN REGEXP_LIKE('abc123', '^\w+$') THEN 'Y' ELSE 'N' END AS result
FROM dual;

REGEXP_LIKE는 WHERE나 조건절에서 사용하는 함수이다.