Sắp xếp cột theo phần số trong chuỗi (ví dụ: 'John_120') trong MySQL

Khi cột lưu chuỗi có cấu trúc 'Tên_Số' (ví dụ: 'John_120', 'Mike_178'), sắp xếp theo chữ cái (ORDER BY cot) sẽ cho kết quả sai (120 > 178 theo chữ cái). Cần tách phần số và ép kiểu sang số để sắp xếp đúng.

Giải pháp: SUBSTRING_INDEX() + ORDER BY

SUBSTRING_INDEX(str, delim, count) tách chuỗi theo dấu phân cách.

• count > 0: lấy phần bên trái (trước dấu phân cách thứ count).
• count < 0: lấy phần bên phải (sau dấu phân cách thứ |count| từ cuối).

Ví dụ minh họa

mysql> CREATE TABLE DemoTable1502 (StudentId VARCHAR(40));
mysql> INSERT INTO DemoTable1502 VALUES
    -> ('John_120'), ('John_201'), ('Mike_178'), ('Bob_198');

Tách và sắp xếp theo phần số (phần sau '_'):

mysql> SELECT
    ->   SUBSTRING_INDEX(StudentId, '_', 1) AS LeftPart,
    ->   SUBSTRING_INDEX(StudentId, '_', -1) AS RightPart
    -> FROM DemoTable1502
    -> ORDER BY CAST(RightPart AS UNSIGNED);

Kết quả (sắp xếp số 120, 178, 198, 201):

+----------+-----------+
| LeftPart | RightPart |
+----------+-----------+
| John     |       120 |
| Mike     |       178 |
| Bob      |       198 |
| John     |       201 |
+----------+-----------+

Mở rộng

• Nếu chỉ cần sắp xếp bảng gốc: ORDER BY CAST(SUBSTRING_INDEX(StudentId, '_', -1) AS UNSIGNED).
• Phân cách bằng nhiều ký tự: dùng SUBSTRING_INDEX lồng nhau hoặc REGEXP_SUBSTR (MySQL 8.0+).
• Hiệu năng: Biểu thức trong ORDER BY không dùng index. Với bảng lớn, hãy tách phần số vào cột riêng StudentNumber INT + index.