Clever Geek Handbook

How to find max of mysql varchar column

I have a table like this with a var char field reference_number

in fact I need to get the maximum number in that field

 <<student>>

 |`id` | `reference_number`(varchar(25))
 --------------------------
 | 1   | L250
 | 2   | SP521
 | 3   | S120
 | 4   | SP500
 | 5   | S122

the desired output is 521 because if we avoid a non-numeric value then it will look like this

|`id` | `reference_number`
 --------------------------
 | 1   | 250
 | 2   | 521
 | 3   | 120
 | 4   | 500
 | 5   | 122

how to get value 521 from table

+3


source to share


2 answers


I assume you have extracted the 'reference_number' as shown in the second snippet from the first snippet. if yes try:



select max(cast (reference_number as int)) from student
+1


source


To get the number 521 (and all the numbers from the reference_number column) you can try:

SELECT * 
FROM yourtable
WHERE reference_number REGEXP '^[0-9]+$';


And then you can add expression ordering.

+1


source






More articles:


All Articles