Developer - 9 months ago 35
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.

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'