MySQL에서 문자와 숫자가 섞인 VARCHAR 데이터 정렬하는 방법

MySQL에서 '101J', '100A'처럼 숫자와 문자가 혼합된 VARCHAR 타입의 데이터를 정렬하려면 ORDER BY 절을 활용할 수 있습니다. 다만 단순히 문자열 기준으로 정렬하면 의도한 순서대로 나오지 않기 때문에, 약간의 트릭이 필요합니다. 이 글에서는 예제를 통해 그 방법을 단계별로 살펴보겠습니다.

예제 테이블 생성

먼저 실습에 사용할 테이블을 생성합니다.

mysql> create table DemoTable
    -> (
    -> StudentCode varchar(20)
    -> );
Query OK, 0 rows affected (0.57 sec)

데이터 삽입

INSERT 명령을 사용해 몇 개의 레코드를 삽입합니다.

mysql> insert into DemoTable values('101J');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values('100A');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('100B');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('101S');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('103M');
Query OK, 1 row affected (0.15 sec)

저장된 데이터 확인

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select * from DemoTable;

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

+-------------+
| StudentCode |
+-------------+
| 101J        |
| 100A        |
| 100B        |
| 101S        |
| 103M        |
+-------------+
5 rows in set (0.00 sec)

문자와 숫자가 섞인 VARCHAR 정렬 쿼리

핵심은 StudentCode+0을 정렬 기준으로 활용하는 것입니다. MySQL에서 VARCHAR 값에 0을 더하면 값의 앞부분에 있는 숫자가 자동으로 숫자형으로 변환됩니다. 따라서 첫 번째 정렬 기준으로 StudentCode+0을, 두 번째 기준으로 원래 문자열인 StudentCode를 지정하면 원하는 결과를 얻을 수 있습니다.

mysql> select * from DemoTable order by StudentCode+0, StudentCode;

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

+-------------+
| StudentCode |
+-------------+
| 100A        |
| 100B        |
| 101J        |
| 101S        |
| 103M        |
+-------------+
5 rows in set, 5 warnings (0.00 sec)

결과를 보면 숫자 부분(100, 101, 103)을 기준으로 먼저 오름차순 정렬된 뒤, 같은 숫자를 가진 항목끼리는 문자 부분(A, B, J, S, M)이 알파벳순으로 정렬된 것을 확인할 수 있습니다.

참고 사항

위 방식은 문자열을 숫자로 암시적(implicit)으로 변환하기 때문에 실행 시 경고(warning)가 발생할 수 있습니다. 프로덕션 환경에서는 CAST(StudentCode AS UNSIGNED)처럼 CAST 함수를 사용해 명시적으로 변환해 주는 것이 안전하고 권장되는 방법입니다.