Как выбрать максимум смешанного столбца string/int?

Предположим, что у меня есть таблица, содержащая столбец для номера счета-фактуры, тип данных - VARCHAR со смешанными значениями string/int, например:

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

Я попытался выбрать max из него, но он возвращается с "HKL9", а не с самым высоким значением "HKL15".

SELECT MAX( invoice_number )
FROM `invoice_header`

Ответ 1

HKL9 (строка) больше, чем HKL15, потому что они сравниваются как строки. Один из способов решения вашей проблемы - определить функцию столбца, которая возвращает только числовую часть номера счета.

Если все номера счетов начинаются с HKL, вы можете использовать:

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

Требуется номер invoice_number, исключая 3 первых символа, преобразуется в int и выбирает max из него.

Ответ 2

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

Ответ 3

Ваша проблема связана скорее с определением и дизайном.

Выберите номер счета с наивысшим идентификатором или DATE, или - если они действительно не коррелируют с "наивысшим номером счета-фактуры" - укажите дополнительный столбец, который коррелирует с номером счета-фактуры и достаточно прост для понимания плохой базы данных.

select INVOICE_NUMBER 
from INVOICE_HEADER
order by ID desc limit 1;

Это не то, что база данных не достаточно умна. Это то, что вы задаете ей неправильный вопрос.

Ответ 4

Это также должно работать

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

Ответ 5

Через некоторое время поиска я нашел самое простое решение.

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