MySQL: หาค่าสูงสุด (MAX) ของคอลัมน์ผสม String/Int ด้วย SUBSTRING และ CAST

เมื่อคอลัมน์เก็บข้อมูลผสมระหว่าง String และ Number (เช่น INV129, INV145) และต้องการหาค่าตัวเลขสูงสุด ต้องแยกส่วนตัวเลขออกมา แปลงเป็นตัวเลข แล้วค่อยหา MAX

รูปแบบคำสั่ง

SELECT MAX(CAST(SUBSTRING(column_name, start_pos, length) AS UNSIGNED)) AS max_value
FROM table_name;

คำอธิบาย:

• SUBSTRING(col, start, len) - ตัดส่วนตัวเลขออกมา
• CAST(... AS UNSIGNED) - แปลงสตริงเป็นตัวเลข (Unsigned Integer)
• MAX() - หาค่าสูงสุดของตัวเลข

ตัวอย่างการใช้งาน

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

CREATE TABLE StringIntMixHighestDemo (
    InvoiceId INT NOT NULL AUTO_INCREMENT,
    InvoiceNumber VARCHAR(20),
    PRIMARY KEY (InvoiceId)
);

2. เพิ่มข้อมูล InvoiceNumber (Prefix: INV + Number)

INSERT INTO StringIntMixHighestDemo (InvoiceNumber) VALUES
('INV129'), ('INV122'), ('INV1'), ('INV145'),
('INV19'), ('INV134'), ('INV135'), ('INV10'),
('INV100'), ('INV121');

ข้อมูลในตาราง:

+-----------+---------------+
| InvoiceId | InvoiceNumber |
+-----------+---------------+
|         1 | INV129        |
|         2 | INV122        |
|         3 | INV1          |
|         4 | INV145        |
|         5 | INV19         |
|         6 | INV134        |
|         7 | INV135        |
|         8 | INV10         |
|         9 | INV100        |
|        10 | INV121        |
+-----------+---------------+

3. หาค่าตัวเลขสูงสุด (ตัด INV ออก = 3 ตัวอักษร)

SELECT MAX(CAST(SUBSTRING(InvoiceNumber, 4, LENGTH(InvoiceNumber) - 3) AS UNSIGNED)) AS HighestValue
FROM StringIntMixHighestDemo;

ผลลัพธ์:

+--------------+
| HighestValue |
+--------------+
|          145 |
+--------------+

คำอธิบาย SUBSTRING

• SUBSTRING(InvoiceNumber, 4, ...) - เริ่มต้นที่ตำแหน่ง 4 (ข้าม 'INV')
• LENGTH(InvoiceNumber) - 3 - ความยาวส่วนที่เหลือ (ทั้งหมด - 3 ตัวแรก)
• ได้สตริง: '129', '122', '1', '145', '19', '134', '135', '10', '100', '121'

กรณี Prefix ความยาวไม่คงที่

หาก Prefix ไม่คงที่ (INV1, INVOICE123, BILL99) ใช้ SUBSTRING_INDEX หรือ REGEXP:

-- สมมติ Prefix จบด้วยตัวอักษร ตัวเลขเริ่มต้นต่อจากนั้น
SELECT MAX(CAST(REGEXP_SUBSTR(InvoiceNumber, '[0-9]+$') AS UNSIGNED)) 
FROM StringIntMixHighestDemo;

(REGEXP_SUBSTR ต้องการ MySQL 8.0+)

วิธีทางเลือก: ใช้ REPLACE

SELECT MAX(CAST(REPLACE(InvoiceNumber, 'INV', '') AS UNSIGNED)) AS HighestValue
FROM StringIntMixHighestDemo;

วิธีนี้ง่ายกว่าหาก Prefix คงที่เสมอ

ข้อควรระวัง

• การใช้ Function บนคอลัมน์ใน WHERE/ORDER BY/MAX จะ ไม่ใช้ Index ได้ (Non-SARGable)
• แนะนำ: แยกเก็บ Prefix และ Number ในคอลัมน์ต่างกัน หรือใช้ Generated Column (MySQL 5.7+)
• Generated Column:

ALTER TABLE StringIntMixHighestDemo
ADD COLUMN InvoiceNum INT GENERATED ALWAYS AS (CAST(SUBSTRING(InvoiceNumber, 4) AS UNSIGNED)) STORED,
ADD INDEX idx_invoice_num (InvoiceNum);

แล้วใช้ SELECT MAX(InvoiceNum) FROM ... ได้เร็วและใช้ Index