mysql get max number from a string field

Question

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.

• PM 77-1: Are you saying that you want to remove /, convert the result to Number and then find their MAX?
• Developer: I need this ''2016/0470' record. as this is the max number after the slash
• PM 77-1: Are you guaranteed to have a slash in each and every record? If so, is it always in 5th position?
• vaso123: Just do what @PM77-1 sad: SELECT MAX(REPLACE(columname, '/', '')) FROM tablename
• AxelH: @PM77-1, and karacsi_maci, the leading 0 might be a problem here, if you had 2016/0422 and 2016/470, the result is 20160422 and 2016470

Answer 1

If columname follows that pattern YEAR/0000, you can use SUBSTRING function from MySQL to remove the part of the string you don't want.

SELECT value FROM (
 SELECT CAST(SUBSTRING(columname, 0, 4) AS UNSIGNED) as year, CAST(SUBSTRING(columname FROM 6) AS UNSIGNED) as value FROM tablename
) total
ORDER BY year DESC, value DESC
LIMIT 1;

• PM 77-1: You need CAST/CONVERT to a number, otherwise "0740" will not be the maximum.
• Dez: Just was thinking on that issue. Editing.
• Developer: Thanks for the response. I'm looking for the max number after '2016/'
• Dez: @Developer I updated the answer to fulfill your needs.
• Developer: thanks for the update, but its not giving me the result for '2016/', its giving for '2011/' record

Answer 2

There are lots of suggestions given as answers already. But some of those seem overkill to me.

Seems like the only change needed to the OP query is the expression in the ORDER BY clause.

Instead of:

 ORDER BY id

We just need to order by the numeric value following the slash. And there are several approaches, several expressions, that will get that from the example data.

Since the query already includes a condition columname LIKE '2016/%'

We can get the characters after the first five characters, and then convert that string to a numeric value by adding zero.

 ORDER BY SUBSTRING(columname,6) + 0 DESC

If we only want to return one row, add

 LIMIT 1

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring

If we only want to return the numeric value, we could use the same expression in the SELECT list, in addition columnname.

This isn't the only approach. There are lots of other approaches that will work, and don't use SUBSTRING.

• Developer: Thanks for getting me right here from my query. but I'm not too sure how it works, for numbers like 2016/0001, 2016/0022, 2016/0322. where i can have multiple zeros too
• spencer7593: As a demonstration: SELECT '0407' + 0 will return numeric value 407. Basically, to perform the + 0 operation, MySQL will need to convert the string value '0407' into a numeric value. The result of that expression is numeric value 407. it doesn't matter how many leading zeros you add to that string, the result is still 407. (The purpose of the SUBSTRING is to remove the leading '2016/' characters, leaving returning the characters following.
• spencer7593: Note that the expression in the selected answer from Shadow is doing the same thing... removing the '2016/' and using a + 0 operation to convert to numeric.

Answer 3

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'

• Developer: Thanks for the response. it should give me the max number for the year 2016
• Shadow: It will give you back the max number after the / character across the entire table. You need to provide additional filters if you have other data than 2016 in your table.
• Developer: I got other years with bigger numbers in the table, that I could not figure out how to write query for.
• Developer: Thanks for your time. Big thanks to everyone and community. this fixed my problem. Thank you very much @Shadow

Answer 4

Try like this:

SELECT 
 MAX(CAST(SUBSTRING(t.name,
 LOCATE('/', t.name) + 1)
 AS UNSIGNED)) AS max_value
FROM
 tablename AS t;

Answer 5

You can try with this little uggly approach:

 SELECT t.id, t2.secondNumber FROM table AS t
 JOIN (SELECT id, 
 CONCAT(SUBSTRING(field,1,5),
 if(SUBSTRING(SUBSTRING(field, 6),1,1)='0', 
 SUBSTRING(field, 6),
 SUBSTRING(field,7)
 )
 ) as secondNumber FROM table ) AS t2 ON t2.id=t.id
 ORDER BY t2.secondNumber DESC

Would be valid only if the 0 (zeroes) before the second number (after the slash) are no more than 1.

Or if the year doesn`t matter you can try to order them only by the second number if it is ok:

SELECT t.id, t2.secondNumber FROM table AS t
JOIN (SELECT id, 
 if(SUBSTRING(SUBSTRING(field, 6),1,1)='0', 
 SUBSTRING(field, 6),
 SUBSTRING(field,7)
 ) as secondNumber FROM table ) AS t2 ON t2.id=t.id
ORDER BY t2.secondNumber DESC