Oracle 정규표현식 함수 정리

문자열 데이터를 다루다 보면 단순한 LIKE만으로는 처리하기 어려운 경우가 있다.

예를 들어 다음과 같은 작업이다.

• 문자열이 숫자로만 구성되어 있는지 검사
• 이메일과 비슷한 형식인지 검사
• 문자열에서 숫자만 제거하거나 추출
• 쉼표로 구분된 문자열에서 특정 번째 값 추출
• 특정 패턴이 문자열에 몇 번 등장하는지 계산

Oracle에서는 이러한 작업을 위해 REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_COUNT와 같은 정규표현식 함수를 제공한다.

---

1. 정규표현식 기본 패턴

정규표현식은 일반 문자와 특별한 의미를 갖는 메타 문자를 조합해 문자열의 패턴을 표현한다.

예를 들어 [0-9]+는 숫자가 1개 이상 연속되는 문자열을 의미하고,
^[0-9]+$는 문자열 전체가 숫자로만 구성되어 있음을 의미한다.

.

임의의 문자 1개를 의미한다.

a.c

예를 들어 다음 문자열이 매칭될 수 있다.

abc
a1c
a-c

a와 c 사이에 어떤 문자든 정확히 한 글자가 존재하면 된다.

---

^

문자열의 시작을 의미한다.

^A

다음 문자열은 매칭된다.

Apple
ABC
A123

반면 다음 문자열은 매칭되지 않는다.

BA
123A

즉, A가 문자열의 첫 번째 위치에 있어야 한다.

---

$

문자열의 끝을 의미한다.

Z$

다음과 같이 Z로 끝나는 문자열을 찾는다.

ABCZ
123Z
Z

---

*

앞의 패턴이 0번 이상 반복되는 것을 의미한다.

ab*

다음 문자열이 매칭될 수 있다.

a
ab
abb
abbb

여기서 *가 적용되는 대상은 바로 앞의 b이다.

따라서 b가 없어도 되고 여러 번 나타나도 된다.

---

+

앞의 패턴이 1번 이상 반복되는 것을 의미한다.

ab+

다음 문자열은 매칭된다.

ab
abb
abbb

하지만 다음 문자열은 매칭되지 않는다.

a

*와 달리 최소 한 번은 나타나야 한다.

---

?

앞의 패턴이 0번 또는 1번 나타나는 것을 의미한다.

ab?

다음 두 형태가 가능하다.

a
ab

b가 두 번 이상 반복되는 abb는 이 패턴 자체와는 일치하지 않는다.

---

{n}

앞의 패턴이 정확히 n번 반복되는 것을 의미한다.

[0-9]{3}

숫자 3개가 연속되는 부분을 의미한다.

123
007
999

---

{n,}

앞의 패턴이 n번 이상 반복되는 것을 의미한다.

[0-9]{2,}

숫자가 최소 2개 이상 연속되는 부분을 찾는다.

12
123
123456

---

{n,m}

앞의 패턴이 최소 n번, 최대 m번 반복되는 것을 의미한다.

[0-9]{2,4}

숫자가 2개 이상 4개 이하 연속되는 패턴을 의미한다.

12
123
1234

---

|

OR 조건을 의미한다.

A|B

A 또는 B에 매칭된다.

A
B

---

()

여러 패턴을 하나의 그룹으로 묶는다.

(ab)+

여기서 +는 b에만 적용되는 것이 아니라 (ab)라는 그룹 전체에
적용된다.

ab
abab
ababab

즉, ab라는 문자열이 한 번 이상 반복되는 패턴이다.

---

[]

문자 집합을 의미하며 대괄호 안에 지정한 문자 중 한 글자와 매칭된다.

[abc]

다음 문자 중 하나와 매칭된다.

a
b
c

범위를 지정할 수도 있다.

[0-9]
[A-Z]
[a-z]

각각 숫자 한 글자, 영문 대문자 한 글자, 영문 소문자 한 글자를 의미한다.

---

[^]

대괄호 내부에서 ^를 사용하면 부정 문자 집합을 의미한다.

[^0-9]

숫자가 아닌 문자 한 글자와 매칭된다.

예를 들어 다음 문자열에서

ABC123

A, B, C가 [^0-9]에 해당한다.

^는 위치에 따라 의미가 다르다. 정규표현식 맨 앞의 ^는 문자열의
시작을 의미하지만, 문자 집합 [] 내부의 첫 위치에서 사용한 ^는 해당
문자 집합의 부정을 의미한다.

---

2. 문자열 전체 검사와 부분 매칭의 차이

정규표현식을 사용할 때 특히 주의해야 하는 부분이 문자열 일부가 패턴에 맞는 것과 문자열 전체가 패턴에 맞는 것의 차이다.

[0-9]

위 패턴은 숫자 한 글자를 찾는다.

따라서 다음 문자열도 숫자 부분이 존재하므로 매칭된다.

ABC1DEF

반면 문자열 전체가 숫자로만 구성되어 있는지 검사하려면 시작과 끝을 함께
지정해야 한다.

^[0-9]+$

구조를 나누어 보면 다음과 같다.

^
│
│   [0-9]+
│      │
│      └─ 숫자가 1개 이상
│
└─ 문자열 시작

            $
            │
            └─ 문자열 끝

따라서 다음 문자열은 매칭된다.

123
007
123456

다음 문자열은 매칭되지 않는다.

ABC123
123ABC
12A34

같은 방식으로 문자열 전체의 문자 종류를 검사할 수 있다.

^[0-9]+$       전체 문자열이 숫자로만 구성
^[A-Za-z]+$    전체 문자열이 영문자로만 구성
^[가-힣]+$      전체 문자열이 한글 음절로만 구성

[가-힣]은 일반적인 완성형 한글 음절 범위를 검사할 때 유용하지만,
모든 Unicode 한글 표현을 포괄하는 패턴은 아니다.

---

3. REGEXP_LIKE

REGEXP_LIKE는 문자열이 특정 정규표현식 패턴과 매칭되는지 검사한다.

기본 형태는 다음과 같다.

REGEXP_LIKE(문자열, 정규표현식, 옵션)

일반적인 LIKE가 %, _를 이용한 단순 패턴 검색이라면 REGEXP_LIKE는 반복 횟수, 문자 집합, 문자열 시작과 끝 등 훨씬 복 잡한 조건을 표현할 수 있다.

예를 들어 email 컬럼이 이메일 형식에 가까운 문자열인지 검사해보자.

SELECT *
FROM employees
WHERE REGEXP_LIKE(
    email,
    '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

패턴을 나누어 보면 다음과 같다.

^[A-Za-z0-9._%+-]+
        │
        └─ @ 앞부분

@
│
└─ @ 문자

[A-Za-z0-9.-]+
        │
        └─ 도메인 부분

\.
│
└─ 점(.) 문자

[A-Za-z]{2,}$
        │
        └─ 영문자 2개 이상으로 끝남

여기서 .은 원래 임의의 문자 한 개라는 특별한 의미를 가지므로, 실제 점 문자를 표현하기 위해 \.을 사용한다.

옵션을 사용하면 대소문자 구분 등의 매칭 방식을 지정할 수도 있다.

SELECT *
FROM employees
WHERE REGEXP_LIKE(ename, '^smith$', 'i');

i 옵션은 대소문자를 구분하지 않도록 한다. 따라서 SMITH, Smith, smith 등이 매칭될 수 있다.

---

4. REGEXP_REPLACE

REGEXP_REPLACE는 정규표현식에 매칭되는 부분을 다른 문자열로 치환한다.

기본적인 형태는 다음과 같다.

REGEXP_REPLACE(문자열, 패턴, 변경할 문자열)

예를 들어 문자열에 공백이 여러 개 연속해서 들어 있다고 해보자.

SELECT REGEXP_REPLACE(
    'abc   def     ghi',
    ' +',
    ' '
) AS result
FROM dual;

결과는 다음과 같다.

abc def ghi

패턴 ' +'는 다음과 같이 해석할 수 있다.

' '
 │
 └─ 공백

+
│
└─ 앞의 공백이 1번 이상 반복

따라서 연속된 여러 공백을 하나의 공백으로 치환한다.

실무에서 자주 사용할 수 있는 패턴은 다음과 같다.

-- 숫자만 남기기
REGEXP_REPLACE(col, '[^0-9]', '')

-- 영문자만 남기기
REGEXP_REPLACE(col, '[^A-Za-z]', '')

-- 공백 문자 제거
REGEXP_REPLACE(col, '\s', '')

-- 연속된 일반 공백을 하나로 변경
REGEXP_REPLACE(col, ' +', ' ')

-- 영문/숫자/한글 음절을 제외한 문자 제거
REGEXP_REPLACE(col, '[^A-Za-z0-9가-힣]', '')

예를 들어 전화번호에서 숫자만 남기고 싶다면 다음과 같이 사용할 수 있다.

SELECT REGEXP_REPLACE(
    '010-1234-5678',
    '[^0-9]',
    ''
) AS phone_no
FROM dual;

결과는 다음과 같다.

01012345678

[^0-9]가 숫자가 아닌 문자를 의미하므로 -가 제거된다.

---

5. REGEXP_SUBSTR

REGEXP_SUBSTR는 문자열에서 정규표현식에 매칭되는 문자열 자체를 추출한다.

기본 형태는 다음과 같다.

REGEXP_SUBSTR(문자열, 패턴, 시작위치, 몇번째매칭)

예를 들어 주문번호가 포함된 문자열에서 주문번호만 추출해보자.

SELECT REGEXP_SUBSTR(
    'Order No: ABC-12345',
    '[A-Z]+-[0-9]+'
) AS order_no
FROM dual;

결과는 다음과 같다.

ABC-12345

패턴은 다음과 같이 해석된다.

[A-Z]+
│
└─ 대문자 1개 이상

-
│
└─ 하이픈 문자

[0-9]+
│
└─ 숫자 1개 이상

쉼표로 구분된 문자열에서 두 번째 값을 가져오는 것도 가능하다.

SELECT REGEXP_SUBSTR(
    'apple,banana,orange',
    '[^,]+',
    1,
    2
) AS second_value
FROM dual;

결과는 다음과 같다.

banana

각 인수의 의미는 다음과 같다.

apple,banana,orange
│
└─ 대상 문자열

[^,]+
│
└─ 쉼표가 아닌 문자가 1개 이상

1
│
└─ 첫 번째 문자부터 검색

2
│
└─ 두 번째 매칭 결과 반환

실제 매칭 과정은 다음과 같다.

apple,banana,orange
───── ────── ──────
  1      2       3

따라서 두 번째 매칭 결과인 banana가 반환된다.

---

6. REGEXP_INSTR

REGEXP_INSTR는 패턴에 매칭된 문자열 자체가 아니라 매칭된 위치를 반환한다.

기본 형태는 다음과 같다.

REGEXP_INSTR(문자열, 패턴)

예를 들어 다음 문자열에서 숫자가 시작되는 위치를 찾아보자.

SELECT REGEXP_INSTR(
    'abc123def',
    '[0-9]+'
) AS pos
FROM dual;

결과는 다음과 같다.

4

문자 위치를 표시하면 다음과 같다.

문자 : a b c 1 2 3 d e f
위치 : 1 2 3 4 5 6 7 8 9
            ↑
        숫자 시작 위치

[0-9]+에 처음 매칭되는 문자열은 123이고 이 문자열은 네 번째 위치에서 시작한다. 따라서 4가 반환된다.

REGEXP_SUBSTR와 비교하면 차이가 명확하다.

REGEXP_SUBSTR → 무엇이 매칭되었는가?
REGEXP_INSTR  → 어디에서 매칭되었는가?

---

7. REGEXP_COUNT

REGEXP_COUNT는 문자열에서 정규표현식 패턴이 몇 번 매칭되는지 반환한다.

기본 형태는 다음과 같다.

REGEXP_COUNT(문자열, 패턴)

예를 들어 쉼표의 개수를 계산해보자.

SELECT REGEXP_COUNT(
    'apple,banana,orange',
    ','
) AS comma_count
FROM dual;

결과는 다음과 같다.

2

숫자가 몇 개 있는지가 아니라 숫자 그룹이 몇 번 등장하는지 계산할 수도 있다.

SELECT REGEXP_COUNT(
    'A12 B34 C567',
    '[0-9]+'
) AS number_group_count
FROM dual;

결과는 다음과 같다.

3

매칭되는 문자열은 다음 세 개다.

A12 B34 C567
 └┘  └┘  └─┘
 12  34   567

여기서 [0-9]가 아니라 [0-9]+를 사용했다는 점이 중요하다.

SELECT REGEXP_COUNT(
    'A12 B34 C567', 
    '[0-9]'
)
FROM dual;

위와 같이 작성하면 각각의 숫자 한 글자가 별도의 매칭이므로 결과는
7이다.

반면 [0-9]+는 연속된 숫자를 하나의 그룹으로 매칭하므로 결과는 3이다.

---

8. Oracle 정규표현식 함수 비교

각 함수의 차이는 결국 패턴을 찾은 뒤 무엇을 반환하거나 수행하는가에 있다.

함수 역할 대표적인 사용 목적

---

REGEXP_LIKE : 패턴 매칭 여부 검사 형식 검증, 조건 검색
REGEXP_REPLACE : 매칭 문자열 치환 문자 제거, 데이터 정제
REGEXP_SUBSTR : 매칭 문자열 반환 특정 값 추출
REGEXP_INSTR : 매칭 위치 반환 특정 패턴의 위치 검색
REGEXP_COUNT : 매칭 횟수 반환 구분자, 패턴 등장 횟수 계산

예를 들어 다음 문자열이 있다고 해보자.

ABC-123-DEF

숫자 123을 대상으로 생각하면 각 함수의 역할은 다음과 같이 구분할 수
있다.

ABC-123-DEF
    └─┘
     │
     ├─ REGEXP_LIKE    → 숫자 그룹이 존재하는가?
     ├─ REGEXP_REPLACE → 숫자 그룹을 다른 값으로 바꾼다
     ├─ REGEXP_SUBSTR  → "123"을 반환한다
     ├─ REGEXP_INSTR   → 숫자 그룹의 시작 위치를 반환한다
     └─ REGEXP_COUNT   → 숫자 그룹이 몇 번 등장하는지 반환한다

---

9. 정규표현식 사용 시 주의할 점

정규표현식은 복잡한 문자열 조건을 간결하게 표현할 수 있지만, 단순 문자열 비교보다 처리 비용이 커질 수 있다.

예를 들어 단순히 특정 접두어로 시작하는 데이터를 찾는 조건이라면 다음과 같이 LIKE로 충분히 표현할 수 있다.

WHERE col LIKE 'ABC%'

이를 굳이 다음처럼 정규표현식으로 작성할 필요는 없다.

WHERE REGEXP_LIKE(col, '^ABC')

특히 대량 데이터의 WHERE 조건에서 컬럼에 정규표현식 함수를 적용하면 일반적인 B-Tree 인덱스를 그대로 활용하기 어려운 실행 계획이 나올 수 있으므로, 정규표현식은 복잡한 패턴 검증이나 문자열 가공이 실제로 필요한 경우에 사용하는 것이 좋다.

또한 정규표현식은 비슷해 보여도 앵커의 유무에 따라 의미가 크게 달라진다.

[0-9]+

위 패턴은 문자열 중간에 숫자 그룹이 하나라도 있으면 매칭된다.

ABC123DEF

반면 다음 패턴은

^[0-9]+$

문자열의 시작부터 끝까지 숫자로만 구성되어 있어야 한다.

정규표현식을 작성할 때는 항상 부분 문자열을 찾는 것인지, 문자열 전체의 형식을 검사하는 것인지 먼저 구분하는 것이 중요하다.

---

정리

Oracle의 정규표현식 함수는 복잡한 문자열 검사와 가공을 SQL 내부에서
처리할 수 있게 해준다.

REGEXP_LIKE
    └─ 패턴과 일치하는지 검사

REGEXP_REPLACE
    └─ 패턴에 일치하는 문자열 치환

REGEXP_SUBSTR
    └─ 패턴에 일치하는 문자열 추출

REGEXP_INSTR
    └─ 패턴에 일치하는 위치 반환

REGEXP_COUNT
    └─ 패턴이 등장하는 횟수 반환

정규표현식을 사용할 때 가장 먼저 익혀두면 좋은 패턴은 다음과 같다.

.            임의의 문자 1개
^            문자열 시작
$            문자열 끝
*            0번 이상 반복
+            1번 이상 반복
?            0번 또는 1번
{n}          정확히 n번
{n,}         n번 이상
{n,m}        n번 이상 m번 이하
|            OR
()           그룹
[]           문자 집합
[^]          부정 문자 집합

특히 [0-9]+처럼 문자열 일부를 찾는 패턴과 ^[0-9]+$처럼 문자열 전체를 검사하는 패턴의 차이를 이해해두면 정규표현식을 사용할 때 발생하는 많은 혼동을 줄일 수 있다.