How to get MAX value of numeric values in varchar column in mysql

How to get MAX value of numeric values in varchar column in mysql

Lets say that I have a table which contains a column for invoice number, the data type is VARCHAR with mixed string/int values like:

invoice_number

**************

    HKL1

    HKL2

    HKL3

    .....

    HKL12

    HKL13

    HKL14

    HKL15

I tried to select max of it, but it returns with "HKL9", not the highest value "HKL15".

SELECT MAX( invoice_number )

FROM `invoice_header`

solution:

SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table