MySQL : how can i convert string to int

[Anon]

Hello,

got a problem

Here's the situation :

Having two fields who i have to evaluate which one is greater ..

Problem is that they can not be of a numeric type because then a 0 will appear when it has to be void ...

Now when i try to evaluate like

... where field1 > field2

and field1 = '4' and field2 = '12' for obvious string comparision reasons the first field is evaluated is the greater one ..

How can i convert a string to a numeric field ?

I know mySql converts a string automatically to an integer when evaluating a string and a numeric field but how can i force this on an evaluation of two strings ???

thanks in advance

[[deleted]]

Change th table from string to integer, and insert NULL when it should be void.

[Anon]

can't do this because the table has a couple of thousands records already that i don't want to change manually

[[deleted]]

You don't have to. If all the fields contain a string representation of a value interger, then youn only have to change the column type from string to integer and you're done.

[Anon]

that's the problem, some fields have a NULL value in those fields and when i change the data type to an int there are all 0's and that's not the intention of course

already tried the field data type conversion but ended up with restoring the db_structure with a lot of problems so i don't want to do that all over again

[[deleted]]

Then create a new column, use an update statement to copy all the not-null values to the new column, and leave the remaining columns NULL. Then swap the column names and you should be done.

[Anon]

While you're at it, Take a lookise here:

http://www.zend.com/manual/language.types.type-juggling.php

and

here:
http://www.zend.com/manual/function.settype.php

You can pull the value, settype it, compare, and then settype it back 😉

[Anon]

Hi,

You could use the following query:

... where int4(field1) > int4(field2);

I hope this helps you..

-Pinkesh

[[deleted]]

We're talking about MySQL, not postgres.

[Anon]

Ok, anyway there's a way to do it with MySQL!

If for some reason you don't want to change the type and you want to compare strings or part of strings like they were numbers, simply use CAST function (Mysql 4.0.2):

select ... where CAST(StrNum AS UNSIGNED) < CAST(StrNum2 AS UNSIGNED) ...

Or sum 0 to your columns (Mysql 3.23):

select ... where StrNum+0 < StrNum2+0 ...

regards, Tomas