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

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.

Are you saying that you want to remove /, convert the result to Number and then find their MAX? — PM 77-1
– PM 77-1, Commented Oct 21, 2016 at 14:47
I need this ''2016/0470' record. as this is the max number after the slash — Developer
– Developer, Commented Oct 21, 2016 at 14:47
Are you guaranteed to have a slash in each and every record? If so, is it always in 5th position? — PM 77-1
– PM 77-1, Commented Oct 21, 2016 at 14:50
Just do what @PM77-1 sad: SELECT MAX(REPLACE(columname, '/', '')) FROM tablename; — vaso123
– vaso123, Commented Oct 21, 2016 at 14:51
@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 — AxelH
– AxelH, Commented Oct 21, 2016 at 14:54

Dez · Accepted Answer · 2016-10-21 15:09:28Z

3

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;

edited Oct 21, 2016 at 15:09

answered Oct 21, 2016 at 14:52

Dez

5,8878 gold badges47 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PM 77-1 Over a year ago

You need CAST/CONVERT to a number, otherwise "0740" will not be the maximum.

Dez Over a year ago

Just was thinking on that issue. Editing.

Developer Over a year ago

Thanks for the response. I'm looking for the max number after '2016/'

Dez Over a year ago

@Developer I updated the answer to fulfill your needs.

Developer Over a year ago

thanks for the update, but its not giving me the result for '2016/', its giving for '2011/' record

spencer7593 · Accepted Answer · 2016-10-21 15:21:13Z

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.

edited Oct 21, 2016 at 15:21

answered Oct 21, 2016 at 15:15

spencer7593

109k15 gold badges122 silver badges148 bronze badges

3 Comments

Developer Over a year ago

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 Over a year ago

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 Over a year ago

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.

Shadow · Accepted Answer · 2016-10-21 15:18:12Z

1

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'

edited Oct 21, 2016 at 15:18

answered Oct 21, 2016 at 14:54

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

4 Comments

Developer Over a year ago

Thanks for the response. it should give me the max number for the year 2016

Shadow Over a year ago

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 Over a year ago

I got other years with bigger numbers in the table, that I could not figure out how to write query for.

Developer Over a year ago

Thanks for your time. Big thanks to everyone and community. this fixed my problem. Thank you very much @Shadow

buildok · Accepted Answer · 2016-10-21 15:03:25Z

1

Try like this:

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

answered Oct 21, 2016 at 15:03

buildok

7956 silver badges7 bronze badges

Comments

krasipenkov · Accepted Answer · 2016-10-21 15:05:04Z

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

Collectives™ on Stack Overflow

mysql get max number from a string field

5 Answers 5

5 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related