Welcome to my blog, hope you enjoy reading
RSS

Sunday 16 June 2013

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

0 comments: