MySQL Natural sort (제목으로 정렬하기) 길이가 다른 데이터 정렬하기

나랑 똑같은 걸 원하는 사람이 있군....ㅋㅋㅋ 오늘 ..구글링

다음과 같은 데이터가 있다. ORDER BY title로 정렬하는 경우 원하는 결과가 출력되지 않고 있다.

mysql> SELECT * FROM chapter ORDER BY title;
+------------+
| title      |
+------------+
| Chapter 1  |
| Chapter 11 |
| Chapter 12 |
| Chapter 2  |
| Chapter 3  |
| Chapter 4  |
+------------+
6 rows in set (0.00 sec)
내가 원하는 결과는 다음과 같다. 어떻게 할 수 있는가?

+------------+
| title      |
+------------+
| Chapter 1  |
| Chapter 2  |
| Chapter 3  |
| Chapter 4  |
| Chapter 11 |
| Chapter 12 |
+------------+

ORDER BY title로는 질문자가 원하는 결과를 얻을 수 없다. 그렇다고 MySQL이 잘못된 결과를 출력한 것은 아니다. 질문자가 원하는 정렬을 NATURAL SORT라고 한다. 필자가 알기로는 MySQL에서 NATURAL SORT를 완벽하게 하는 방법은 없는 것으로 알고 있다.

질문자의 데이터에서는 다음의 SQL로 원하는 결과를 얻을 수는 있다.

SELECT title
FROM chapter
ORDER BY LENGTH(title), title;

+------------+
| title      |
+------------+
| Chapter 1  |
| Chapter 2  |
| Chapter 3  |
| Chapter 4  |
| Chapter 11 |
| Chapter 12 |
+------------+
6 rows in set (0.00 sec)
역순 정렬도 무리 없이 된다.

SELECT title
FROM chapter
ORDER BY LENGTH(title) DESC, title DESC;

+------------+
| title      |
+------------+
| Chapter 12 |
| Chapter 11 |
| Chapter 4  |
| Chapter 3  |
| Chapter 2  |
| Chapter 1  |
+------------+
6 rows in set (0.00 sec)
본 질문의 경우 모든 title이 동일한 “Chapter”라는 문자열로 시작했기 때문에 NATUAL SORT가 잘 작동하였다.

NATURAL SORT의 다른 방법으로는 title에서 “Chapter” 문자를 없애버린 뒤에 남아 있는 숫자 부분을 INT형으로 CAST한 뒤에 정렬하는 방법도 있으나 SQL이 복잡해진다는 단점이 있다. 그러나만약 숫자가 0 이상의 양수가 아닌 음수를 포함하는 경우는 방금 설명한 것과 같이 문자열을 모두 제거한 뒤 숫자 부분만 가지고 정렬하도록 해야 할 것이다.