MySQL에서 문자와 숫자가 혼합된 열의 문자 값 정렬하기

개요

MySQL에서 'J23', 'C13'처럼 문자와 숫자가 혼합된 값이 저장된 열을 단순히 ORDER BY로 정렬하면 문자열 기준으로 처리되어 의도한 순서대로 정렬되지 않을 수 있습니다. 이 글에서는 LEFT(), SUBSTR(), CAST() 함수를 조합하여 알파벳 순으로 먼저 정렬한 뒤, 숫자 부분을 수치로 변환해 정렬하는 방법을 예제와 함께 살펴보겠습니다.

테이블 생성하기

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

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

샘플 데이터 삽입하기

INSERT 명령을 사용하여 테이블에 레코드를 추가합니다.

mysql> insert into DemoTable values('J23');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('C13');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values('A61');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('D56');
Query OK, 1 row affected (0.11 sec)

저장된 데이터 확인하기

SELECT 문을 사용하여 테이블의 모든 레코드를 조회합니다.

mysql> select * from DemoTable;

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+------+
| Code |
+------+
| J23  |
| C13  |
| A61  |
| D56  |
+------+
4 rows in set (0.00 sec)

문자 값을 정렬하는 쿼리

아래 쿼리는 첫 번째 문자(알파벳)를 기준으로 먼저 정렬한 후, 나머지 숫자 부분을 부호 없는 정수로 변환하여 오름차순으로 정렬합니다.

mysql> select * from DemoTable
    -> order by left(Code,1),
    -> cast(substr(Code, 2) as unsigned);

위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.

+------+
| Code |
+------+
| A61  |
| C13  |
| D56  |
| J23  |
+------+
4 rows in set (0.05 sec)

쿼리 동작 원리

LEFT(Code, 1): 각 값에서 첫 번째 문자만 추출합니다. 예를 들어 'J23'에서는 'J'를 반환하며, 이 값을 기준으로 알파벳순 정렬이 먼저 이루어집니다.

SUBSTR(Code, 2): 두 번째 문자부터 끝까지, 즉 숫자 부분만 추출합니다. 이후 CAST(... AS UNSIGNED)를 통해 해당 값을 부호 없는 정수로 변환하여 숫자 크기대로 정렬합니다.

이 두 단계를 결합하면 'A61', 'C13', 'D56', 'J23'처럼 알파벳이 우선 정렬되고, 같은 알파벳 내에서는 숫자 크기 순으로 자연스럽게 정렬된 결과를 얻을 수 있습니다. 코드나 제품 번호처럼 문자 접두사와 숫자가 결합된 데이터를 관리할 때 매우 유용한 기법입니다.