MySQL에서 문자열 데이터에서 숫자 추출하기
MySQL 테이블에 저장된 문자열 데이터에서 숫자 부분만 추출하고 싶다면 CONVERT() 함수 또는 정규식(Regular Expression)을 활용할 수 있습니다. 특히 CONVERT() 함수는 하나의 데이터 타입을 다른 타입으로 변환해 주는 역할을 하며, 이를 통해 문자열 속 숫자를 손쉽게 가져올 수 있습니다.
예제를 통해 자세히 살펴보겠습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다.
mysql> create table textIntoNumberDemo
-> (
-> Name varchar(100)
-> );
Query OK, 0 rows affected (0.47 sec)
2. 샘플 데이터 삽입하기
테이블에 몇 개의 레코드를 삽입합니다. 각 레코드는 이름과 하이픈(-)으로 구분된 숫자로 구성되어 있습니다.
mysql> insert into textIntoNumberDemo values('John-11');
Query OK, 1 row affected (0.11 sec)
mysql> insert into textIntoNumberDemo values('John-12');
Query OK, 1 row affected (0.17 sec)
mysql> insert into textIntoNumberDemo values('John-2');
Query OK, 1 row affected (0.11 sec)
mysql> insert into textIntoNumberDemo values('John-4');
Query OK, 1 row affected (0.14 sec)3. 전체 레코드 확인하기
저장된 모든 레코드를 조회해 보겠습니다.
mysql> select *from textIntoNumberDemo;
실행 결과는 다음과 같습니다.
+---------+
| Name |
+---------+
| John-11 |
| John-12 |
| John-2 |
| John-4 |
+---------+
4 rows in set (0.00 sec)
4. 숫자 추출 쿼리 작성하기
문자열에서 숫자를 추출하는 기본 문법은 다음과 같습니다.
SELECT yourColumnName,
CONVERT(SUBSTRING_INDEX(yourColumnName,'-',-1),UNSIGNED INTEGER) AS yourVariableName
FROM yourTableName
order by yourVariableName;
여기서 SUBSTRING_INDEX(컬럼명, '-', -1)는 하이픈(-)을 기준으로 마지막 부분, 즉 숫자 부분을 잘라내고, CONVERT(..., UNSIGNED INTEGER)가 해당 문자열을 부호 없는 정수로 변환합니다.
5. 실제 쿼리 실행 결과
위 문법을 적용한 실제 쿼리는 다음과 같습니다.
mysql> SELECT Name,CONVERT(SUBSTRING_INDEX(Name,'-',-1),UNSIGNED INTEGER) AS MyNumber
-> FROM textIntoNumberDemo
-> order by MyNumber;
실행 결과를 확인해 보겠습니다.
+---------+----------+
| Name | MyNumber |
+---------+----------+
| John-2 | 2 |
| John-4 | 4 |
| John-11 | 11 |
| John-12 | 12 |
+---------+----------+
4 rows in set (0.00 sec)
결과를 보면 'John-11', 'John-12', 'John-2', 'John-4'와 같은 문자열에서 숫자 부분만 성공적으로 분리되어 추출된 것을 확인할 수 있습니다. 특히 ORDER BY 절 덕분에 숫자 값 기준으로 오름차순 정렬(2 → 4 → 11 → 12)이 올바르게 적용된 점도 주목할 만합니다. 만약 단순히 문자열 그대로 정렬했다면 'John-11'이 'John-2'보다 앞에 위치하는 사전순 정렬 결과가 나왔을 것입니다.
이처럼 SUBSTRING_INDEX()와 CONVERT() 함수를 조합하면 MySQL에서 혼합된 문자열 데이터로부터 숫자를 간편하게 추출하고 정렬까지 처리할 수 있습니다.