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.
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.
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
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.