CODEWITHSUNDEEP
mysql
Cris
Cris

Reputation: 5007

Mysql numbers in varchar columns query issue

I have this column in a talbe which for historical reason is a string but nowdays new values are put in are numbers.

I have to find the biggest value from an interval of numbers but the values are string in the DB :(

If op_calcul would have been a number it would have been trivial sql:

Select MAX(op_calcul) FROM nom_prod where op_calcul  >=500 and op_calcul < 5000

but having them as varchar put me into some trouble.

Tried different scenarios but did not come with a solution yet.

Any hint ?

Thanks

Upvotes: 1

Views: 267

Answers (3)

Alex Pliutau
Alex Pliutau

Reputation: 21957

SELECT MAX(CAST(`op_calcul` AS SIGNED))
FROM `nom_prod`
WHERE `op_calcul`  >= 500
AND `op_calcul` < 5000;

Upvotes: 1

Corbin
Corbin

Reputation: 33437

SELECT MAX(CAST(op_calcul as SIGNED)) FROM nom_prod...

Should do the trick. You might need to cast the WHERE clauses as well.

Ideally though, for performance reasons on both the MAX and the WHERE, you should use an integer column. Is there non-numeric or out of range data in it or something? It should be fairly trivial to change the column type if not.

Upvotes: 1

DhruvPathak
DhruvPathak

Reputation: 43225

use mysql cast to convert data into required datatypes.

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast

Note however that it does affect query performance.

Upvotes: 1

Related Questions