HOW select min from cast varchar to int in mysql

Question

How to convert varchar into double value in mysql

See, i have table column in varchar but it has only numbers. In that i want to select min max of the value.

Please check the below query, in which I'm getting a syntax error.

select 
    MAX(CAST(ch1 as INT)) as max_ch1,
    MIN(CAST(ch1 as INT)) as min_ch1 
from t9;

Please refer to below sqlfiddle

wwwhhhyyyy? :-( SELECT MIN(ch1+0),MAX(ch1+0) FROM t9; – Strawberry Oct 11 '13 at 11:49 — Strawberry, Oct 11 '13 at 11:49
Please format the code part for a better view. – Oct 11 '13 at 11:51 — , Oct 11 '13 at 11:51

juergen d · Answer 1 · 2020-06-21T04:57:05.127

7

You can force an automatic numeric cast when using an operation like adding

select MAX(ch1+0) as max_ch1,
       MIN(ch1+0) as min_ch1 
from t9

edited Jun 21 '20 at 04:57

answered Oct 11 '13 at 11:53

juergen d

score 7 · Accepted Answer · answered Oct 11 '13 at 11:54

7

I think this is what you are looking for:

SELECT
  MIN(CAST(CH1 AS SIGNED)),
  MAX(CAST(CH1 AS SIGNED))
FROM t9

Working SQLFiddle here.

You have to CAST the value as SIGNED, which corresponds to INTEGER in MySQL. More information about this, here.

answered Oct 11 '13 at 11:54

Radu Gheorghiu

2 Answers2