สร้างตารางและข้อมูลตัวอย่าง
mysql> create table DemoTable2006 ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserCode varchar(20) );
mysql> insert into DemoTable2006(UserCode) values('John_12');
mysql> insert into DemoTable2006(UserCode) values('John_34');
mysql> insert into DemoTable2006(UserCode) values('John_56');
mysql> insert into DemoTable2006(UserCode) values('Chris_101');
mysql> insert into DemoTable2006(UserCode) values('Chris_103');
mysql> insert into DemoTable2006(UserCode) values('Chris_106');
ข้อมูลในตาราง
+--------+-----------+
| UserId | UserCode |
+--------+-----------+
| 1 | John_12 |
| 2 | John_34 |
| 3 | John_56 |
| 4 | Chris_101 |
| 5 | Chris_103 |
| 6 | Chris_106 |
+--------+-----------+
Query ดึงแถว John ที่มีตัวเลขมากที่สุด
mysql> select * from DemoTable2006
where UserCode like 'John%'
order by cast(substring(UserCode from 7) as signed) desc
limit 1;
ผลลัพธ์
+--------+----------+
| UserId | UserCode |
+--------+----------+
| 3 | John_56 |
+--------+----------+
อธิบายการทำงาน
WHERE UserCode LIKE 'John%' กรองเฉพาะที่ขึ้นต้นด้วย "John"SUBSTRING(UserCode FROM 7) ดึงตัวอักษรตั้งแต่ตำแหน่งที่ 7 (หลัง "John_" = 5 ตัว + 1 = 6? รอ... "John_" = 5 chars, ตำแหน่ง 6 คือตัวแรกของตัวเลข)- แก้ไข: "John_" = 5 ตัวอักษร → ตำแหน่ง 6 เริ่มตัวเลข →
SUBSTRING(UserCode, 6) หรือ SUBSTRING(UserCode FROM 6)
CAST(... AS SIGNED) แปลงสตริงตัวเลขเป็น Integer เพื่อเรียง 12, 34, 56 → 56 มากที่สุดORDER BY ... DESC LIMIT 1 เรียงมาก→น้อย เอาแถวแรก
ฟังก์ชันสตริงที่ใช้บ่อย
| ฟังก์ชัน | คำอธิบาย | ตัวอย่าง |
|---|
| SUBSTRING(str, pos, len) | ดึงส่วนของสตริง | SUBSTRING('John_56', 6) = '56' |
| SUBSTRING_INDEX(str, delim, count) | ดึงส่วนก่อน/หลัง Delimiter | SUBSTRING_INDEX('John_56', '_', -1) = '56' |
| LOCATE(substr, str) | หาตำแหน่ง Substring | LOCATE('_', 'John_56') = 5 |
| CAST(expr AS TYPE) | แปลงชนิดข้อมูล | CAST('56' AS SIGNED) = 56 |
วิธีที่ทนทานกว่า (ไม่ต้องนับตำแหน่ง)
-- ใช้ SUBSTRING_INDEX แยกด้วย _
ORDER BY CAST(SUBSTRING_INDEX(UserCode, '_', -1) AS SIGNED) DESC
-- หรือ REGEXP_SUBSTR (MySQL 8.0)
ORDER BY CAST(REGEXP_SUBSTR(UserCode, '[0-9]+$') AS SIGNED) DESC
