MySQL에서 EMP1, EMP2 같은 열 값에서 문자열(접두사)을 제거하는 방법

데이터베이스 작업을 하다 보면 EMP9, EMP4, EMP8처럼 공통된 접두사가 붙어 있는 값을 만나고, 여기서 문자열 부분을 제거하고 숫자만 추출해야 하는 경우가 있습니다. 이럴 때 MySQL에서는 RIGHT() 함수와 LENGTH() 함수를 함께 사용하면 간단하게 해결할 수 있습니다.

아래 예제를 통해 단계별로 살펴보겠습니다.

1단계: 테이블 생성하기

먼저 테스트용 테이블을 생성합니다.

mysql> create table DemoTable1540
    -> (
    -> EmployeeCode varchar(20)
    -> );
Query OK, 0 rows affected (0.39 sec)

2단계: 데이터 삽입하기

INSERT 명령을 사용해 테이블에 샘플 데이터를 입력합니다.

mysql> insert into DemoTable1540 values('EMP9');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1540 values('EMP4');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1540 values('EMP8');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1540 values('EMP6');
Query OK, 1 row affected (0.12 sec)

3단계: 저장된 데이터 확인하기

SELECT 문으로 테이블의 전체 레코드를 조회해 봅니다.

mysql> select * from DemoTable1540;

실행 결과는 다음과 같습니다.

+--------------+
| EmployeeCode |
+--------------+
| EMP9         |
| EMP4         |
| EMP8         |
| EMP6         |
+--------------+
4 rows in set (0.00 sec)

4단계: 열 값에서 문자열 제거하기

이제 본격적으로 열 값에서 'EMP' 문자열을 제거하고 숫자만 남기는 쿼리를 실행해 보겠습니다.

mysql> select right(EmployeeCode,length(EmployeeCode)-3) as onlyDigit from DemoTable1540;

실행 결과는 다음과 같습니다.

+-----------+
| onlyDigit |
+-----------+
| 9         |
| 4         |
| 8         |
| 6         |
+-----------+
4 rows in set (0.00 sec)

쿼리 동작 원리

위 쿼리가 어떻게 동작하는지 간단히 정리하면 다음과 같습니다.

LENGTH(EmployeeCode)는 전체 문자열의 길이를 반환합니다. 예를 들어 'EMP9'의 경우 길이가 4입니다. 여기서 3을 빼면 접두사 'EMP'(3글자)를 제외한 나머지 길이, 즉 1이 됩니다.

RIGHT(EmployeeCode, n)은 문자열의 오른쪽 끝에서부터 n개의 문자를 반환합니다. 따라서 RIGHT(EmployeeCode, LENGTH(EmployeeCode) - 3)은 앞의 3글자('EMP')를 잘라내고 나머지 문자만 돌려줍니다.

참고: 다른 대안 방법

상황에 따라 아래와 같은 함수들도 유용하게 사용할 수 있습니다.

SUBSTRING(EmployeeCode, 4) — 4번째 문자부터 끝까지 반환하여 동일한 결과를 얻을 수 있습니다.

REPLACE(EmployeeCode, 'EMP', '') — 특정 문자열을 찾아 제거할 때 사용하며, 접두사가 항상 일정한 경우에 적합합니다.

접두사 길이가 고정되어 있다면 위 예제처럼 RIGHT()와 LENGTH() 조합이 가장 직관적이며 안정적인 방법입니다.