📘 [SQL 함수 정리] 정규표현식(Regex) 정리

🔍 정규표현식이란?

정규표현식(Regular Expression, Regex)은 문자열의 패턴을 정의하고, 그 패턴에 일치하는 문자열을 찾는 도구입니다.
Oracle에서는 REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR 등을 통해 활용됩니다.

---

✅ 기본 구조 예시

SELECT REGEXP_SUBSTR('ORA-00600 Oracle SQL-Server 50', '[^0-9]+') AS result FROM DUAL;

설명:

• '[^0-9]+': 숫자가 아닌 문자가 1개 이상 반복되는 패턴
• 결과: 'ORA-' (맨 앞 숫자가 아닌 문자열 추출)

---

🧩 정규식 함수 설명

함수 이름	설명	예시	결과
REGEXP_SUBSTR	문자열 중 정규식과 일치하는 부분 문자열 추출	REGEXP_SUBSTR(‘abc123def’, ‘[0-9]+’)	123
REGEXP_INSTR	정규식에 일치하는 위치 반환	REGEXP_INSTR(‘abc123’, ‘[0-9]+’)	4
REGEXP_REPLACE	정규식과 일치하는 문자열을 다른 값으로 치환	REGEXP_REPLACE(‘abc123’, ‘[0-9]’, ‘*’)	abc***
REGEXP_LIKE	정규식 패턴과 일치하는 여부를 반환(TRUE/FALSE)	WHERE REGEXP_LIKE(col, ‘^[A-Z]{3}[0-9]{3}$’)	ABC123 매칭  → TRUE

 

🧩 정규식 패턴 설명

패턴	의미	예시
^	문자열 시작	^abc→ 'abc'로 시작
$	문자열 끝	123$ → '123'로 끝남
+	1개 이상 반복	a+ → a, aa, aaa
*	0개 이상 반복	a* → '', a, aa
?	0개 또는 1개	a? → '', a
.	아무 문자 1개	a.b → 'acb' 등
{n}	n개 반복	a{3}  → aaa
{n, m}	n~m개 반복	a{2,4} → aa, aaa, aaaa
[0-9]	숫자 1개	1, 2, ...
[^0-9]	숫자가 아닌 문자 1개	a, -, X
[A-Z]{3}	대문자 3글자	ABC, XYZ
[0-9]{3}	숫자 3자리	123, 999

 

• 'ABC123'은 어떤 정규식에 매칭될까?

정규식	매칭 결과	이유
^[A-Z]{3}[0-9]{3}$	✅ OK	대문자 3 + 숫자 3 정확히 일치
^[A-Z]{3}[0-9]+$	✅ OK	대문자 3 + 숫자 1개 이상이므로 만족
^[a-zA-Z0-9_]+$	✅ OK	영문자, 숫자만 포함된 전체 문자열
^[A-Z]{2}[0-9]{3}$	❌ NO	대문자 2개가 아니기 때문
^[A-Z]{3}[0-9]{4}$	❌ NO	숫자 4개가 아니기 때문

 

---

🎯  응용 예제

1. 대문자 3글자 + 숫자 1개 이상 매칭

WHERE REGEXP_LIKE(col, '^[A-Z]{3}[0-9]+$')

• ^: 시작
• [A-Z]{3}: 대문자 3개
• [0-9]+: 숫자 1개 이상
• $: 끝

📌 정확히 일치하는 패턴만 허용함
📌  예: ABC123, XYZ9
❌ 예: AB123, ABC123X, 123ABC

 

2. 숫자로 끝나는 문자열만 찾기

WHERE REGEXP_LIKE(col, '[0-9]$')

• 숫자로 끝나는 문자열이면 모두 허용
  📌  예: hello123, X9, A0
  ❌ 예: 123X, abc

 

3. 숫자를 제외한 첫문자 찾기

SELECT REGEXP_SUBSTR('a0b8c7', '[^0-9]') AS 결과 FROM DUAL;
-- 결과 : a

• 0~9가 아닌 문자 중 가장 첫 번째로 등장하는 것 한 개만 반환됩니다.
• REGEXP_SUBSTR는 기본적으로 처음 매칭되는 항목 하나만 반환합니다.

 

4. 문자열이 특정 패턴에 맞는지 확인

SELECT CASE 
         WHEN REGEXP_LIKE('User_01', '^[a-zA-Z0-9_]+$') 
         THEN 'OK' 
         ELSE 'NO' 
       END 결과 
FROM DUAL;
-- 결과 : OK

• 0~9가 아닌 문자 중 가장 첫 번째로 등장하는 것 한 개만 반환됩니다.
• 정규식 설명

^	문자열의 시작을 의미
[a-zA-Z0-9_]	소문자, 대문자, 숫자, 밑줄(_) 중 하나
+	1개 이상 반복됨
$	문자열의 끝을 의미

📌 예: 'ABC123' → ✅ 매칭

📌 예: 'AB123' → ❌ 안 맞음

📌 예: 'ABC1234' → ❌ 안 맞음

 

5. 문자열에서 숫자 3자리 찾기

SELECT REGEXP_SUBSTR('전화번호는 042-123-4567입니다', '[0-9]{3}') AS 결과
FROM DUAL;
-- 결과 : 042

 

 

6. 문자열이 특정 패턴에 맞는지 확인

SELECT CASE 
         WHEN REGEXP_LIKE('ABC123', '^[A-Z]{3}[0-9]{3}$') 
         THEN 'OK' 
         ELSE 'NO' 
       END 결과 
FROM DUAL;
-- 결과 : OK

📌 예: 'AB123' → ❌ NO  →  대문자 2개밖에 없음

📌 예: 'ABC1234' → ❌ NO → 숫자가 4개로 초과

📌 예: 'abc123' → ❌ NO → 대문자가 아니라 소문자

📌 예: 'A1B2C3' → ❌ NO →  대문자/숫자 순서 불일치

📌 예: 'XYZ999' → ✅  OK → 대문자 3개 + 숫자 3개 정확히

---

정규표현식은 특정 패턴을 식별하고자 할 때 매우 강력한 도구입니다.
특히 Oracle SQL에서는 REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE 등과 함께 자주 쓰이므로, 다양한 패턴에 익숙해지면 SQL의 활용도가 더욱 높아집니다 💪