จัดเรียงคอลัมน์ alphanumeric ใน MySQL ให้ถูกต้อง

คอลัมน์ที่ผสมตัวเลขและตัวอักษร (เช่น S/TU/10, S/TU/100) ใช้ ORDER BY ธรรมดาจะเรียงแบบสตริง (100 ก่อน 10) ต้องแยกส่วนตัวเลขออกมาแปลงเป็นตัวเลข

สร้างตารางตัวอย่าง

mysql> CREATE TABLE DemoTable (
    -> StudentId VARCHAR(100)
    -> );

เพิ่มข้อมูลตัวอย่าง

mysql> INSERT INTO DemoTable VALUES('S/TU/100');
mysql> INSERT INTO DemoTable VALUES('S/TU/1000');
mysql> INSERT INTO DemoTable VALUES('S/TU/10');

แสดงข้อมูลก่อนเรียง

mysql> SELECT * FROM DemoTable;

ผลลัพธ์:

+-----------+
| StudentId |
+-----------+
| S/TU/100  |
| S/TU/1000 |
| S/TU/10   |
+-----------+

คำสั่งเรียงให้ถูกต้อง (10, 100, 1000)

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 |
+-----------+

คำอธิบาย: SUBSTRING(StudentId, 1, 9) = ส่วนคงที่ 'S/TU/' | SUBSTRING(StudentId, 10)*1 = ส่วนตัวเลข คูณ 1 เพื่อแปลงเป็นตัวเลข แล้วเรียงตัวเลขได้ถูกต้อง