MySQL – How to Select Max Value of a Varchar Column

In some cases, you might want to select maximum value from a column with varchar datatype but when you use MAX() function normally, It will fetch wrong value. But, it can be possibly done by filtering number from the string using SUBSTRING() function and CAST() Function.

MySQL Max Value Varchar Column


For instance, say, you have a table called students with student admission details. It has a column called adm_no. And the adm_no column is set to mysql varchar data type in order to store alpha numeric values.

Normally admission numbers be like, some characters followed auto increasing numbers (eg ADM01). When you add a new student you have to get max value in order to assign next value since you cannot set mysql auto increment attribute to a varchar column.

So you have to use SUBSTRING() function to split the number from the string and convert the string using CAST() function and then you can easily get maximum value using MAX() functtion.


SELECT MAX(CAST((SUBSTRING([column_name] , [digit])) as UNSIGNED)) FROM `[table_name]`;

SUBSTRING() function will get new value from specified number of digits. If we want to find max value for column with values like (eg ADM1, ADM2, … ADM[n]) we have to give 4 as digit.

Then the CAST() function will convert the string into INT. Then we can find maximum value using MAX().


Consider the table with the values below

MySQL Select Max Value Varchar Column

To get the maximum value from this table, we can use the select query like below

SELECT MAX(CAST((SUBSTRING(adm_no,4)) as UNSIGNED)) FROM `students`;

This query will fetch the maximum value 10. Then we can do whatever needed with the value.

For easy usage you can use the same query like this

SELECT MAX(CAST((SUBSTRING(adm_no,4)) as UNSIGNED)) AS maxval FROM `students`;

That’s it. This query will be pretty handy in some situations. Use this query and comment your feedback below.

Anand Roshan

Author: Anand Roshan

An entrepreneur, programmer and a passionate artist who loves to work independently. Get social: Twitter | Google + | Artist Page

One comment on “MySQL – How to Select Max Value of a Varchar Column”

  1. This only works if you know exactly where the number begins. In your case, the 4th character. What if the user is not restricted in data entry and you can’t rely on everything after the 4th character being numeric?

Leave a Reply

Your email address will not be published. Required fields are marked *