MySQL에서 문자열의 숫자 부분만 추출하는 방법

MySQL 문자열에서 숫자만 추출하기

데이터베이스 작업을 하다 보면 'John19383'처럼 문자와 숫자가 섞여 있는 값에서 숫자 부분만 분리해야 하는 경우가 종종 있습니다. 이 글에서는 MySQL의 REVERSE(), FORMAT(), REPLACE() 함수를 조합하여 문자열 끝에 붙어 있는 숫자만 깔끔하게 추출하는 방법을 예제와 함께 알아보겠습니다.

1. 테이블 생성

먼저 예제에 사용할 테이블을 만듭니다.

mysql> create table DemoTable
    -> (
    -> StudentId varchar(100)
    -> );
Query OK, 0 rows affected (0.71 sec)

2. 데이터 삽입

INSERT 명령으로 테스트용 레코드를 몇 개 추가합니다.

mysql> insert into DemoTable values('John19383');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable values('Carol9999');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable values('David123456');
Query OK, 1 row affected (0.15 sec)

3. 저장된 데이터 확인

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

mysql> select *from DemoTable;

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

+-------------+
| StudentId   |
+-------------+
| John19383   |
| Carol9999   |
| David123456 |
+-------------+
3 rows in set (0.00 sec)

4. 숫자만 추출하는 쿼리

문자열에서 숫자 부분만 추출하려면 아래 쿼리를 사용합니다.

mysql> SELECT replace(reverse(FORMAT(reverse(StudentId), 0)), ',', '') as OnlyDigit from DemoTable;

출력 결과

각 행에서 문자가 제거되고 숫자만 남은 것을 확인할 수 있습니다.

+-----------+
| OnlyDigit |
+-----------+
| 19383     |
| 9999      |
| 123456    |
+-----------+
3 rows in set (0.05 sec)

쿼리 동작 원리

이 쿼리가 어떻게 동작하는지 단계별로 살펴보겠습니다.

• REVERSE(StudentId) : 문자열을 거꾸로 뒤집습니다. 예를 들어 'John19383'은 '38391nhoJ'가 됩니다.
• FORMAT(..., 0) : 뒤집힌 문자열은 숫자로 시작하므로 MySQL이 이를 숫자로 암시적으로 변환한 뒤, 천 단위 콤마가 포함된 형식('38,391')으로 반환합니다.
• REVERSE(...) : 다시 문자열을 뒤집으면 '193,83'이 되어 원래의 숫자 순서로 돌아옵니다.
• REPLACE(..., ',', '') : 마지막으로 콤마를 제거하면 순수한 숫자 '19383'만 남습니다.

참고: REGEXP_REPLACE를 사용한 대안

위 방법은 숫자가 문자열 끝에 위치한 경우에 정확하게 동작합니다. 숫자가 중간에 있거나 여러 곳에 흩어져 있다면 MySQL 8.0 이상에서 제공하는 REGEXP_REPLACE()를 사용하는 것이 더 안전합니다.

mysql> SELECT REGEXP_REPLACE(StudentId, '[^0-9]', '') as OnlyDigit from DemoTable;

정규식 패턴 '[^0-9]'는 숫자가 아닌 모든 문자를 의미하므로, 이를 빈 문자열로 치환하면 어떤 위치의 숫자든 손쉽게 추출할 수 있습니다.