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

ใช้ ORDER BY (col+0), RIGHT(col, 1) แยกส่วนตัวเลขและตัวอักษรท้าย

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

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

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

mysql> INSERT INTO DemoTable VALUES('2X'), ('100Y'), ('100X'), ('2Z'), ('2Y'), ('100Z');

แสดงข้อมูล

mysql> SELECT * FROM DemoTable;

ผลลัพธ์:

+-----------+
| StudentId |
+-----------+
| 2X        |
| 100Y      |
| 100X      |
| 2Z        |
| 2Y        |
| 100Z      |
+-----------+

คำสั่งเรียงตัวเลขก่อน ตัวอักษรท้าย

mysql> SELECT * FROM DemoTable ORDER BY (StudentId + 0), RIGHT(StudentId, 1);

ผลลัพธ์ (2X, 2Y, 2Z, 100X, 100Y, 100Z):

+-----------+
| StudentId |
+-----------+
| 2X        |
| 2Y        |
| 2Z        |
| 100X      |
| 100Y      |
| 100Z      |
+-----------+

คำอธิบาย: StudentId + 0 แปลงส่วนหน้าที่เป็นตัวเลข (2, 100) | RIGHT(StudentId, 1) เอาตัวอักษรสุดท้าย (X, Y, Z) | เรียงตัวเลขก่อน แล้วค่อยตัวอักษร | จะมี warning เพราะการแปลงสตริงที่ไม่ใช่ตัวเลขแต่ MySQL ทำงานได้