MySQL 텍스트 필드에서 숫자만 추출하기
데이터베이스 작업을 하다 보면 텍스트 필드에 숫자와 함께 하이픈(-), 쉼표(,), 공백, 따옴표 같은 불필요한 문자가 섞여 있는 경우를 자주 만나게 됩니다. 이럴 때 MySQL의 REPLACE 함수를 중첩해서 사용하면 손쉽게 숫자만 깔끔하게 추출할 수 있습니다.
1. 테스트용 테이블 생성
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table DemoTable
(
Number text
);
Query OK, 0 rows affected (0.49 sec)2. 샘플 데이터 삽입
INSERT 명령을 사용해 숫자와 특수문자가 섞인 데이터를 입력합니다.
mysql> insert into DemoTable values('7364746464,-');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values('-,8909094556');
Query OK, 1 row affected (0.23 sec)3. 저장된 데이터 확인
SELECT 문으로 테이블의 전체 레코드를 조회해 보면 다음과 같습니다.
mysql> select *from DemoTable;
실행 결과는 아래와 같습니다.
+--------------+ | Number | +--------------+ | 7364746464,- | | -,8909094556 | +--------------+ 2 rows in set (0.00 sec)
4. REPLACE 함수로 숫자만 추출하는 쿼리
텍스트 필드에서 숫자만 추출하려면 REPLACE 함수를 여러 겹으로 중첩하여 하이픈(-), 공백, 큰따옴표(") , 쉼표(,)를 차례대로 제거하면 됩니다.
mysql> select replace(replace(replace(replace(Number, '-', ''), ' ', ''),"",''),',','') as Result from DemoTable;
위 쿼리를 실행하면 다음과 같이 깨끗한 숫자만 출력되는 것을 확인할 수 있습니다.
+------------+ | Result | +------------+ | 7364746464 | | 8909094556 | +------------+ 2 rows in set (0.00 sec)
동작 원리 이해하기
REPLACE 함수는 REPLACE(컬럼명, '찾을 문자열', '바꿀 문자열') 형태로 사용하며, 해당 컬럼에서 찾을 문자열과 일치하는 모든 부분을 지정한 문자열로 치환합니다. 빈 문자열('')로 치환하면 사실상 삭제 효과를 얻을 수 있습니다.
- 첫 번째 REPLACE: 하이픈(-) 제거
- 두 번째 REPLACE: 공백(' ') 제거
- 세 번째 REPLACE: 큰따옴표(") 제거
- 네 번째 REPLACE: 쉼표(,) 제거
이렇게 함수를 중첩하면 하나의 쿼리로 여러 종류의 특수문자를 동시에 처리할 수 있습니다.
참고: MySQL 8.0 이상이라면 REGEXP_REPLACE 활용
MySQL 8.0부터는 정규식을 지원하는 REGEXP_REPLACE 함수를 사용할 수도 있습니다. 아래 쿼리는 숫자가 아닌 모든 문자를 한 번에 제거하므로 더 간결합니다.
mysql> SELECT REGEXP_REPLACE(Number, '[^0-9]', '') AS Result FROM DemoTable;
[^0-9] 패턴은 '숫자가 아닌 모든 문자'를 의미하며, 이를 빈 문자열로 바꾸면 숫자만 남게 됩니다. 제거해야 할 문자의 종류가 많거나 예측하기 어렵다면 REGEXP_REPLACE 방식이 훨씬 효율적입니다.