How to select maximum mixed column string / int?

Question

Suppose I have a table containing a column for the invoice number, the data type is VARCHAR with mixed string / int values, for example:

invoice_number ************** HKL1 HKL2 HKL3 ..... HKL12 HKL13 HKL14 HKL15 

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

 SELECT MAX( invoice_number ) FROM `invoice_header` 

Answer 1

HKL9 (string) is larger than HKL15 because they are compared as strings. One way to solve your problem is to define a column function that returns only the numeric part of the account number.

If all account numbers start with HKL , you can use:

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

The number invoice_number is required, excluding the first 3 characters, is converted to int and selects max from it.

Answer 2

 select ifnull(max(CONVERT(invoice_number, SIGNED INTEGER)), 0) from invoice_header where invoice_number REGEXP '^[0-9]+$' 

Answer 3

Your problem is more about definition and design.

Select the account number with the highest identifier or DATE, or - if they really do not correlate with the "highest invoice number" - indicate an additional column that correlates with the invoice number and is simple enough to understand a bad database.

 select INVOICE_NUMBER from INVOICE_HEADER order by ID desc limit 1; 

It is not that the database is not smart enough. It is that you ask her the wrong question.

Answer 4

This should also work.

 SELECT invoice_number FROM invoice_header ORDER BY LENGTH( invoice_number) DESC,invoice_number DESC LIMIT 0,1 

Answer 5

After some time searching, I found the simplest solution.

 select MAX(CAST(REPLACE(REPLACE(invoice_number , 'HKL', ''), '', '') as int)) from invoice_header