how to select max of mixed string/int column?
select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0)
from invoice_header
where invoice_number REGEXP '^[0-9]+$'
This should work also
SELECT invoice_number
FROM invoice_header
ORDER BY LENGTH( invoice_number) DESC,invoice_number DESC
LIMIT 0,1
HKL9
(string) is greater than HKL15
, because they are compared as strings. One way to deal with your problem is to define a column function that returns only the numeric part of the invoice number.
If all your invoice numbers start with HKL
, then you can use:
SELECT MAX(CAST(SUBSTRING(invoice_number, 4, length(invoice_number)-3) AS UNSIGNED)) FROM table
It takes the invoice_number excluding the 3 first characters, converts to int, and selects max from it.
After a while of searching I found the easiest solution to this.
select MAX(CAST(REPLACE(REPLACE(invoice_number , 'HKL', ''), '', '') as int)) from invoice_header