[SQL] DBMS별 문자열 함수 완벽 비교: SUBSTR, INSTR, 정규표현식(REGEXP)

데이터베이스를 다루는 백엔드 개발자나 데이터 분석가에게 문자열 가공은 피할 수 없는 숙명과 같습니다. 하지만 Oracle에서 잘 돌아가던 쿼리가 MySQL이나 PostgreSQL로 옮기면 에러를 뿜어내는 경우가 허다하죠. 특히 날짜만큼이나 DBMS별로 차이가 큰 영역이 바로 문자열 함수입니다.

오늘은 실무에서 가장 빈번하게 사용하는 문자열 추출, 위치 찾기, 그리고 강력한 정규표현식 활용법까지 3대 DBMS별로 상세히 비교해 보겠습니다.

---

1. 특정 위치의 문자열 추출: SUBSTR vs SUBSTRING

문자열의 특정 시작 지점부터 원하는 길이만큼 잘라내는 함수입니다. 가장 기본적이지만 함수명이 미묘하게 다릅니다.

• Oracle / PostgreSQL: SUBSTR()을 기본으로 사용합니다.
• MySQL: SUBSTRING()이 정식 명칭이지만, 사용자의 편의를 위해 SUBSTR()도 별칭으로 지원합니다.

✅ 실무 예제: 주민번호나 날짜 데이터 자르기

문자열의 시작 위치는 보통 1부터 시작하며, 오라클의 경우 시작 위치에 음수를 넣으면 뒤에서부터 거꾸로 위치를 계산하는 유연함을 보여줍니다.

SQL

-- [Oracle, PostgreSQL] 8자리 날짜에서 연도만 추출
SELECT SUBSTR('19950101', 1, 4) FROM DUAL; -- 결과: '1995'

-- [MySQL] 동일한 결과 출력
SELECT SUBSTRING('19950101', 1, 4); -- 결과: '1995'

---

2. 특정 문자의 위치 찾기: INSTR vs STRPOS

데이터 내에 특정 기호(예: @, -, 공백)가 몇 번째 인덱스에 있는지 알아내는 함수입니다. 이 함수는 단독으로 쓰이기보다 주로 뒤에 나올 SUBSTR과 결합하여 동적인 문자열 추출에 사용됩니다.

• Oracle / MySQL: INSTR(원본문자열, 찾을문자) 방식을 사용합니다.
• PostgreSQL: STRPOS(원본문자열, 찾을문자)를 사용하며, 인자 순서는 동일합니다.

---

3. 실무 응용: 이메일 아이디만 동적으로 추출하기

고정된 길이가 아닌, 특정 문자를 기준으로 앞부분만 잘라야 하는 경우입니다. 실무에서 가장 많이 쓰이는 패턴이니 반드시 숙지해야 합니다.

• Oracle: SUBSTR(email, 1, INSTR(email, '@') - 1)
• MySQL: SUBSTRING(email, 1, INSTR(email, '@') - 1)
• PostgreSQL: SUBSTR(email, 1, STRPOS(email, '@') - 1)

💡 팁: @ 기호의 위치를 찾은 뒤 -1을 해주는 이유는 골뱅이(@) 기호 자체는 결과에서 제외하기 위함입니다. 이러한 조합을 통해 데이터의 길이가 제각각이어도 정확한 아이디 값만 뽑아낼 수 있습니다.

---

4. 문자열 가공의 끝판왕: 정규표현식(REGEXP) 활용

단순한 자르기로 해결되지 않는 복잡한 패턴은 정규표현식이 답입니다. 예를 들어 "전화번호에서 하이픈만 제거하기"나 "숫자가 포함된 행만 찾기" 등에 활용됩니다. DBMS별로 문법 차이가 가장 극명하게 갈리는 영역입니다.

기능	Oracle	MySQL	PostgreSQL
패턴 매칭	REGEXP_LIKE	REGEXP	~ (연산자 사용)
패턴 추출	REGEXP_SUBSTR	REGEXP_SUBSTR	REGEXP_MATCH
패턴 치환	REGEXP_REPLACE	REGEXP_REPLACE	REGEXP_REPLACE

✅ 실무 예제: 지저분한 연락처 데이터 정제 (숫자만 남기기)

데이터에 하이픈(-), 공백, 괄호 등이 섞여 있을 때 숫자만 쏙 골라내는 쿼리입니다.

• Oracle / MySQL: SELECT REGEXP_REPLACE('010-1234-5678', '[^0-9]', '') FROM DUAL;
• PostgreSQL : (PostgreSQL에서 'g' 플래그는 global의 약자로, 일치하는 모든 패턴을 다 바꾸겠다는 의미입니다.)
• SELECT REGEXP_REPLACE('010-1234-5678', '[^0-9]', '', 'g');

---

🏁 한눈에 보는 DBMS별 문자열 함수 요약표

기능	Oracle	MySQL	PostgreSQL
현재 시간 조회	SYSDATE	NOW()	NOW()
문자열 자르기	SUBSTR	SUBSTRING	SUBSTR
문자 위치 찾기	INSTR	INSTR	STRPOS
문자열 합치기	`		또는CONCAT`
NULL 처리	NVL	IFNULL	COALESCE

---

💡 마무리하며: 성능 최적화 포인트

문자열 함수는 매우 편리하지만, WHERE 절에서 컬럼에 직접 함수를 씌우는 행위는 주의해야 합니다.

예를 들어 WHERE SUBSTR(reg_date, 1, 4) = '2026'과 같이 작성하면 해당 컬럼에 인덱스가 있더라도 인덱스를 타지 못하고 Full Table Scan을 하게 됩니다. 가급적 컬럼 데이터는 그대로 두고 비교하는 상수 값을 가공하는 방향으로 쿼리를 작성하는 것이 성능 튜닝의 기본입니다.