Developer Developer - 1 month ago 5
SQL Question

mysql get max number from a string field

I need to get maximum number from a part of the value that generally start with year followed by slash(

/
). So I need a maximum number after the slash(/) but year should be 2016

2016/422
2016/423
2016/469
2016/0470
2014/777
2015/123
2015/989


I tried this query

SELECT columname FROM tablename WHERE columname LIKE '2016/%' ORDER BY id DESC


the above query always giving '2016/469' as first record, how to get '2016/0470' as the maximum number?

any help will be much appreciated.

Thank you.

Answer

You need to split the string into 2 parts and evaluate them as numbers, instead of strings. The following formula will return the number after the / in the fieldname. All functions used below are described in the string functions section of the MySQL documentation. This way you can get the number after the / character, even if it is not year before the /, but sg else. The + 0 converts the string to a number, eliminating any leading 0.

select right(columnname, char_length(columnname)-locate('/',columnname)) + 0
from tablename

Just take the max() of the above expression to get the expected results.

UPDATE:

If you need the original number and the result has to be restricted to a specific year, then you need to join back the results to the original table:

select columnname
from tablename t1
inner join (select max(right(t.columnname, char_length(t.columnname)-locate('/',t.columnname)) + 0) as max_num
            from tablename t
            where left(t.columnname,4)='2016'
           ) t2
           on right(t1.columnname, char_length(1t.columnname)-locate('/',t1.columnname)) + 0 = t2.max_num
where left(t1.columnname,4)='2016'