MySQL에서 숫자가 포함된 문자열을 올바르게 정렬하는 방법 (ORDER BY, SUBSTR, CAST)

MySQL에서 숫자가 포함된 문자열 정렬하기

MySQL에서 'A10', 'A30', 'A12'처럼 문자와 숫자가 섞인 문자열을 단순히 ORDER BY로 정렬하면 사전순(문자열 순)으로 정렬되어 의도와 다른 결과가 나올 수 있습니다. 이런 경우 SUBSTR() 함수와 CAST() 함수를 함께 사용하면 숫자 크기 기준으로 자연스럽게 정렬할 수 있습니다.

기본 문법

SELECT * FROM yourTableName 
ORDER BY 
    SUBSTR(yourColumnName FROM 1 FOR 2),
    CAST(SUBSTR(yourColumnName FROM 2) AS UNSIGNED);

각 부분의 역할은 다음과 같습니다.

• SUBSTR(yourColumnName FROM 1 FOR 2) : 문자열의 앞 2글자(접두사 + 첫 자리 숫자)를 기준으로 1차 정렬합니다.
• CAST(SUBSTR(yourColumnName FROM 2) AS UNSIGNED) : 두 번째 글자부터 끝까지의 숫자 부분을 추출한 뒤, 부호 없는 정수(UNSIGNED)로 변환하여 2차 정렬합니다.

이렇게 하면 숫자 부분이 문자열이 아닌 실제 숫자 값으로 비교되므로, 'A10'이 'A9'보다 앞에 오는 식의 잘못된 정렬을 방지할 수 있습니다. 참고로 FOR 2의 값은 데이터의 접두사 길이에 맞게 조정하시면 됩니다.

1. 예제 테이블 생성

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

mysql> create table OrderByStringWithNumbers
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Words varchar(10),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.86 sec)

2. 샘플 데이터 삽입

INSERT 명령으로 샘플 데이터를 입력합니다.

mysql> insert into OrderByStringWithNumbers(Words) values('A10');
Query OK, 1 row affected (0.19 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A30');
Query OK, 1 row affected (0.19 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A12');
Query OK, 1 row affected (0.13 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A11');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A28');
Query OK, 1 row affected (0.13 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A21');
Query OK, 1 row affected (0.20 sec)

3. 전체 데이터 확인

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

mysql> select *from OrderByStringWithNumbers;

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

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 2  | A30   |
| 3  | A12   |
| 4  | A11   |
| 5  | A28   |
| 6  | A21   |
+----+-------+
6 rows in set (0.00 sec)

현재는 데이터가 입력된 순서 그대로 저장되어 있으며, 아직 정렬되지 않은 상태입니다.

4. 문자열 + 숫자 정렬 쿼리 실행

이제 SUBSTR()과 CAST()를 활용해 숫자가 포함된 문자열을 정렬하는 쿼리를 실행합니다.

mysql> select *from OrderByStringWithNumbers order by
    -> substr(Words from 1 for 2),
    -> cast(substr(Words from 2) AS UNSIGNED);

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

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 4  | A11   |
| 3  | A12   |
| 6  | A21   |
| 5  | A28   |
| 2  | A30   |
+----+-------+
6 rows in set (0.00 sec)

결과를 보면 'A10 → A11 → A12 → A21 → A28 → A30' 순서로, 숫자 값의 크기 기준으로 자연스럽게 정렬된 것을 확인할 수 있습니다. 이 방법은 제품 코드, 항목 번호처럼 접두사 + 숫자 형태의 문자열 데이터를 관리할 때 특히 유용합니다.