จัดเรียง MySQL ตามตัวอักษร (ไม่ใช่ตัวเลข) สำหรับคอลัมน์ที่มีสตริงผสมตัวเลข เช่น '456 John Smith'

เมื่อต้องการจัดเรียงข้อมูลตามตัวอักษรใน MySQL โดยข้ามตัวเลขนำหน้า สามารถใช้ฟังก์ชัน SUBSTRING() ร่วมกับ LOCATE() เพื่อดึงเฉพาะส่วนข้อความหลังช่องว่างแรกได้

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

mysql> create table DemoTable ( 
    Id varchar(100) ); 
Query OK, 0 rows affected (0.65 sec)

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

mysql> insert into DemoTable values('456 John Smith'); 
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('897 Adam Smith'); 
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('1009 Bob Smith'); 
Query OK, 1 row affected (0.13 sec)

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

mysql> select * from DemoTable;

ผลลัพธ์:

+----------------+
| Id             |
+----------------+
| 456 John Smith |
| 897 Adam Smith |
| 1009 Bob Smith |
+----------------+
3 rows in set (0.00 sec)

คำสั่งจัดเรียงตามตัวอักษร (ข้ามตัวเลขนำหน้า)

mysql> select * from DemoTable 
order by SUBSTRING(Id, LOCATE(' ', Id));

ผลลัพธ์หลังจัดเรียง

+----------------+
| Id             |
+----------------+
| 897 Adam Smith |
| 1009 Bob Smith |
| 456 John Smith |
+----------------+
3 rows in set (0.05 sec)

คำอธิบาย: ฟังก์ชัน LOCATE(' ', Id) จะหาตำแหน่งของช่องว่างแรก แล้ว SUBSTRING() จะดึงข้อความตั้งแต่วันั้นไปจนจบ ทำให้การจัดเรียงอิงตามชื่อจริง ไม่ใช่ตัวเลขนำหน้า