Mysql numbers in varchar columns query issue

Question

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

Lightness Races in Orbit · Accepted Answer · 2011-12-16 09:46:56Z

1

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.

edited Dec 16, 2011 at 9:46

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

answered Dec 16, 2011 at 9:42

Corbin

33.6k7 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cris Over a year ago

the column has historical data as varchar like "code 12ac" etc

Corbin Over a year ago

Ah... In that case, you know those rows might not be casted correctly, right? Anything that's not a correctly formed integer will likely be cast to 0. Edit: Was that edit really necessary? Seems kind of superfluous.

Cris Over a year ago

yes but in the range of op_calcul >=500 and op_calcul < 5000 there is no conflictual data...harcoded stuff but is a must

Corbin Over a year ago

The comparison with integers does an implicit cast, though I suppose it will not cause any problems.

DhruvPathak · Accepted Answer · 2011-12-16 09:44:14Z

1

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.

answered Dec 16, 2011 at 9:44

DhruvPathak

43.4k17 gold badges125 silver badges180 bronze badges

Comments

Alex Pliutau · Accepted Answer · 2011-12-16 09:48:20Z

1

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

answered Dec 16, 2011 at 9:48

Alex Pliutau

22k28 gold badges115 silver badges144 bronze badges

1 Comment

Corbin Over a year ago

Mostly opinion, but backticks should typically not be used unless reserved words are being used as entity names (which shouldn't be done). They kill SQL portability (though there really is very little to begin with) and offer no advantage.

Collectives™ on Stack Overflow

Mysql numbers in varchar columns query issue

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related