MySQL: เรียงลำดับตัวเลขในสตริง เช่น CSE 15, CSE 11 ให้ถูกต้องตามค่าตัวเลข

สร้างตารางและข้อมูลตัวอย่าง

mysql> create table DemoTable1969 ( BranchCode varchar(20) );

mysql> insert into DemoTable1969 values('CSE 101');
mysql> insert into DemoTable1969 values('CSE 11');
mysql> insert into DemoTable1969 values('CSE 15');
mysql> insert into DemoTable1969 values('CSE 6');
mysql> insert into DemoTable1969 values('CSE 201');
mysql> insert into DemoTable1969 values('CSE 110');

ข้อมูลก่อนเรียง (เรียงแบบ String ปกติ)

+------------+
| BranchCode |
+------------+
| CSE 101    |
| CSE 11     |
| CSE 15     |
| CSE 6      |
| CSE 201    |
| CSE 110    |
+------------+

Query เรียงตามค่าตัวเลข

ใช้ SUBSTRING + LOCATE ดึงส่วนตัวเลข แล้ว CAST(... AS SIGNED) แปลงเป็นตัวเลข:

mysql> select * from DemoTable1969 order by CAST(SUBSTRING(BranchCode, LOCATE(' ', BranchCode)+1) AS SIGNED) DESC;

ผลลัพธ์ (เรียงจากมากไปน้อย)

+------------+
| BranchCode |
+------------+
| CSE 201    |
| CSE 110    |
| CSE 101    |
| CSE 15     |
| CSE 11     |
| CSE 6      |
+------------+

อธิบายการทำงาน

1. LOCATE(' ', BranchCode) หาตำแหน่ง Space (ใน "CSE 15" อยู่ตำแหน่งที่ 4)
2. SUBSTRING(BranchCode, ... +1) ดึงข้อความหลัง Space ได้ "15"
3. CAST(... AS SIGNED) แปลง "15" → 15 (ตัวเลข)
4. ORDER BY ... DESC เรียงจากมากไปน้อย

รูปแบบอื่นที่สั้นกว่า (MySQL 8.0+)

-- ใช้ REGEXP_SUBSTR (MySQL 8.0)
ORDER BY CAST(REGEXP_SUBSTR(BranchCode, '[0-9]+') AS SIGNED) DESC;

-- หรือใช้ SUBSTRING_INDEX
ORDER BY CAST(SUBSTRING_INDEX(BranchCode, ' ', -1) AS SIGNED) DESC;

กรณีข้อมูลซับซ้อน

ถ้ารูปแบบไม่คงที่ (เช่น "CSE15", "CSE-15", "CSE 015") ควรทำความสะอาดข้อมูลก่อน หรือใช้ Regular Expression ที่ซับซ้อนขึ้น