MySQL에서 영숫자 열을 정렬하는 방법 – SUBSTRING()과 LIKE 연산자 활용

MySQL에서 영숫자가 섞인 문자열 열을 단순히 ORDER BY로 정렬하면 의도한 순서대로 결과가 나오지 않는 경우가 많습니다. 예를 들어 'S/TU/10', 'S/TU/100', 'S/TU/1000' 같은 값은 문자열 비교 방식 때문에 숫자 크기 순서대로 정렬되지 않습니다. 이럴 때 LIKE 연산자와 SUBSTRING() 함수를 함께 활용하면 원하는 순서대로 정렬할 수 있습니다.

1. 테이블 생성

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

mysql> create table DemoTable
    -> (
    -> StudentId varchar(100)
    -> );
Query OK, 0 rows affected (1.54 sec)

2. 데이터 삽입

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

mysql> insert into DemoTable values('S/TU/100');
Query OK, 1 row affected (0.28 sec)

mysql> insert into DemoTable values('S/TU/1000');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DemoTable values('S/TU/10');
Query OK, 1 row affected (0.47 sec)

3. 전체 레코드 조회

select 문으로 테이블의 모든 레코드를 확인합니다.

mysql> select *from DemoTable;

출력 결과

+-----------+
| StudentId |
+-----------+
| S/TU/100  |
| S/TU/1000 |
| S/TU/10   |
+-----------+
3 rows in set (0.00 sec)

위 결과를 보면 문자열 기준 정렬이 적용되어 'S/TU/10'이 맨 아래에 위치하는 등, 숫자 크기 순서와 다르게 정렬된 것을 확인할 수 있습니다.

4. 영숫자 열 정렬 쿼리

다음은 MySQL에서 영숫자 열을 올바르게 정렬하는 쿼리입니다.

mysql> select StudentId from DemoTable where StudentId LIKE 'S%' order by
substring(StudentId,1,9), substring(StudentId,10)*1;

출력 결과

+-----------+
| StudentId |
+-----------+
| S/TU/10   |
| S/TU/100  |
| S/TU/1000 |
+-----------+
3 rows in set (0.00 sec)

이제 'S/TU/10' → 'S/TU/100' → 'S/TU/1000' 순서로 숫자 크기대로 정렬된 것을 볼 수 있습니다.

쿼리 동작 원리

• LIKE 'S%': 'S'로 시작하는 학생 ID만 필터링하여 대상 범위를 좁힙니다.
• substring(StudentId, 1, 9): 문자열 앞부분의 공통 접두사 영역을 추출해 첫 번째 정렬 기준으로 사용합니다.
• substring(StudentId, 10)*1: 10번째 문자부터 추출한 숫자 부분에 *1을 곱해 숫자로 형변환합니다. MySQL에서는 문자열에 산술 연산을 적용하면 자동으로 숫자로 변환되므로, 이를 통해 수치 크기 순서대로 정렬할 수 있습니다.

이처럼 접두사와 숫자 부분을 분리해 정렬하면, 영숫자가 혼합된 ID나 코드 값을 사람이 읽기 자연스러운 순서로 손쉽게 정렬할 수 있습니다.