CODEWITHSUNDEEP
mysqlsql
Developer
Developer

Reputation: 3998

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.

Upvotes: 0

Views: 4779

Answers (5)

spencer7593
spencer7593

Reputation: 108400

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.

Upvotes: 1

Shadow
Shadow

Reputation: 34231

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'

Upvotes: 1

Dez
Dez

Reputation: 5838

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;

Upvotes: 3

krasipenkov
krasipenkov

Reputation: 2029

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

Upvotes: 0

buildok
buildok

Reputation: 785

Try like this:

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

Upvotes: 0

Related Questions