MySQL เรียงลำดับค่า Character-Number Mixed (เช่น J23, C13) ให้ถูกต้อง

เมื่อคอลัมน์มีข้อมูลผสมตัวอักษรนำหน้าตัวเลข (เช่น J23, C13, A61) การเรียงลำดับแบบปกติ (Lexicographic) จะได้ผลลัพธ์ผิด ต้องแยกตัวอักษรและตัวเลขออกมาเรียงแยกกัน

ตัวอย่างปัญหา

mysql> SELECT * FROM DemoTable ORDER BY Code;
+------+
| Code |
+------+
| A61  |
| C13  |
| D56  |
| J23  |  -- ผิด! ควรเป็น J23 ก่อน C13? ไม่ J มาหลัง C
+------+

วิธีแก้: แยกตัวอักษรตัวแรก และตัวเลขส่วนท้าย

1. สร้างตาราง

mysql> CREATE TABLE DemoTable (
    Code VARCHAR(20)
);
Query OK, 0 rows affected (0.70 sec)

2. เพิ่มข้อมูล

mysql> INSERT INTO DemoTable VALUES('J23');
mysql> INSERT INTO DemoTable VALUES('C13');
mysql> INSERT INTO DemoTable VALUES('A61');
mysql> INSERT INTO DemoTable VALUES('D56');

3. คำสั่งเรียงลำดับที่ถูกต้อง

mysql> SELECT * FROM DemoTable
    -> ORDER BY LEFT(Code, 1),           -- 1. เรียงตามตัวอักษรตัวแรก (A, C, D, J)
    -> CAST(SUBSTR(Code, 2) AS UNSIGNED); -- 2. เรียงตามตัวเลขส่วนท้าย (13, 23, 56, 61)

คำอธิบาย:

• LEFT(Code, 1) : ดึงตัวอักษรตัวแรก (Prefix)
• SUBSTR(Code, 2) : ดึงสตริงตั้งแต่ตัวที่ 2 เป็นต้นไป (Suffix ตัวเลข)
• CAST(... AS UNSIGNED) : แปลงสตริงตัวเลขเป็นตัวเลขจริง เพื่อให้ 13 < 23 < 56 < 61 (ไม่ใช่เรียงแบบสตริง '13' < '23' < '56' < '61' ซึ่งก็ถูกต้องเหมือนกันแต่ CAST ปลอดภัยกว่า)

4. ผลลัพธ์ที่ถูกต้อง

+------+
| Code |
+------+
| A61  |
| C13  |
| D56  |
| J23  |
+------+

กรณีซับซ้อนกว่า: Prefix หลายตัวอักษร (เช่น AB12, AB3, AC5)

SELECT * FROM DemoTable
ORDER BY 
    SUBSTRING_INDEX(Code, '[0-9]', 1),  -- แยก Prefix (ก่อนตัวเลขตัวแรก) - ต้องใช้ REGEXP หรือ Logic อื่น
    CAST(SUBSTRING(Code, LENGTH(SUBSTRING_INDEX(Code, '[0-9]', 1)) + 1) AS UNSIGNED);

หมายเหตุ: MySQL ไม่มี SPLIT_PART หรือ REGEXP_REPLACE ก่อน v8.0 กรณีซับซ้อนอาจต้องใช้ Stored Function หรือจัดการที่ Application Layer

สรุป

สำหรับรูปแบบ Letter + Number คงที่ ใช้ ORDER BY LEFT(col, 1), CAST(SUBSTR(col, 2) AS UNSIGNED) แยก Prefix และ Suffix เรียงแยกกัน จะได้ลำดับที่ถูกต้องตาม Logic ธุรกิจ