2

I would like to know how can I get the max value only within the numeric values in a column with char values in a MySql database:

Supposing the column is as follows:

 col  
---------
ALL      
USD      
USD      
RET      
RET      
ACC      
ACC      
ALL      
ALL      
ERR      
ERR      
PRO      
PRO      
1        
1        
2        
2        
3        
3        
4        
4        
5        
5        
6        
6        
7        
7        
8        
8        
9        
9        
AY       
AY       
C        
C        
CF       

I should get 9 as a result of my query.

Thanks in advance.

1 Answer 1

2

If the numbers are all Integer, that you want

SELECT MAX(CAST(col AS SIGNED)) from nextg WHERE col REGEXP '^[0-9]+$';

Like @Akina said in the comments MySQL knows another way

SELECT MAX(col + 0) from nextg WHERE col REGEXP '^[0-9]+$';

See example

If you have also Decimal Numbers.

SELECT MAX(CAST(col AS DECIMAL(8,2))) from nextg WHERE col 
REGEXP '^[0-9]+\\.?[0-9]*$'
3
  • 1
    Should you not cast to number before applying max? Otherwise 9>10 Apr 6, 2020 at 21:14
  • Oh thx missed it
    – nbk
    Apr 6, 2020 at 21:23
  • 1
    SELECT MAX(col + 0) from nextg; is enough. Or SELECT MAX(col + 0) from nextg WHERE col REGEXP '^[0-9]+$'; if values like '99ASD' are present.
    – Akina
    Apr 6, 2020 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.