문자열과 숫자가 섞여 있는 데이터를 사람이 읽는 순서대로 정렬하려면 ORDER BY 절에 여러 조건을 조합해 사용하면 됩니다. 이 글에서는 예제 테이블을 만들고 단계별로 정렬 방법을 살펴보겠습니다.
1. 예제 테이블 생성
먼저 value라는 TEXT 타입 컬럼 하나를 가진 테이블을 생성합니다.
mysql> create table demo18
-> (
-> value text
-> );
Query OK, 0 rows affected (1.18 sec)
2. 샘플 데이터 삽입
INSERT 명령으로 몇 개의 레코드를 추가합니다.
mysql> insert into demo18 values('John Smith');
Query OK, 1 row affected (0.06 sec)
mysql> insert into demo18 values('2J John has 58');
Query OK, 1 row affected (0.17 sec)
mysql> insert into demo18 values('2J John has 9');
Query OK, 1 row affected (0.09 sec)
3. 저장된 데이터 확인
SELECT 문으로 테이블의 레코드를 조회해 보겠습니다.
mysql> select * from demo18;
+----------------+
| value |
+----------------+
| John Smith |
| 2J John has 58 |
| 2J John has 9 |
+----------------+
3 rows in set (0.00 sec)
4. 정렬 쿼리 실행
문자열 끝의 숫자까지 고려해 자연스럽게 정렬하려면 REGEXP_REPLACE, LENGTH, value 세 가지 기준을 ORDER BY에 함께 지정합니다.
mysql> select * from demo18
-> order by regexp_replace(value, '[0-9]*$', ''),
-> length(value),
-> value;
실행 결과는 다음과 같습니다.
+----------------+
| value |
+----------------+
| 2J John has 9 |
| 2J John has 58 |
| John Smith |
+----------------+
3 rows in set (0.14 sec)
정렬 원리 살펴보기
각 정렬 기준이 어떤 역할을 하는지 자세히 보겠습니다.
- regexp_replace(value, '[0-9]*$', ''): 문자열 끝에 붙은 숫자를 모두 제거한 나머지 부분을 기준으로 우선 정렬합니다. 덕분에 공통 접두사를 가진 항목들이 서로 인접하게 묶입니다.
- length(value): 같은 접두사를 가진 항목들 사이에서는 문자열 길이가 짧은 것부터 정렬합니다. 이렇게 하면 "2J John has 9"가 "2J John has 58"보다 앞에 오게 됩니다.
- value: 마지막으로 전체 문자열을 사전식으로 비교해 동일한 값들의 순서를 결정합니다.
일반적인 사전식(lexicographic) 정렬에서는 "58"이 "9"보다 앞에 오는 문제가 발생하지만, 위 방법을 사용하면 숫자 값을 실제 크기 순서대로 인식하는 자연 정렬(natural sort) 효과를 얻을 수 있습니다. 파일 이름, 버전 번호처럼 문자열 뒤에 숫자가 붙는 데이터를 다룰 때 특히 유용합니다.