CAST DECIMAL to INT

Question

I m trying to do this   SELECT CAST columnName AS INT   moreColumns  etc FROM myTable WHERE       I ve looked at the help FAQs here  http   dev mysql com doc refman 5 0 en cast-functions html   it says I can do it like CAST val AS TYPE   but it s not working   Trying to convert a decimal to int  real value is 223 00 and I want 223

User · Answer

From the article you linked to      The type can be one of the following values       BINARY  N        CHAR  N        DATE      DATETIME      DECIMAL  M  D         SIGNED  INTEGER       TIME      UNSIGNED  INTEGER    Try SIGNED instead of INT

User · Answer

Try  cast  columnName as unsigned   unsigned is positive value only  If you want to include negative value  then cast  columnName as signed   The difference between sign  negative include  and unsigned  twice the size of sign  but non-negative

User · Answer

A more optimized way in mysql for this purpose    SELECT columnName DIV 1 AS columnName  moreColumns  etc FROM myTable WHERE       Using DIV 1 is a huge speed improvement over FLOOR  not to mention string based functions like FORMAT  mysql gt  SELECT BENCHMARK 10000000 1234567 DIV 7     -----------------------------------    BENCHMARK 10000000 1234567 DIV 7     -----------------------------------                                    0    -----------------------------------  1 row in set  0 83 sec   mysql gt  SELECT BENCHMARK 10000000 1234567   7     ---------------------------------    BENCHMARK 10000000 1234567   7     ---------------------------------                                  0    ---------------------------------  1 row in set  7 26 sec   mysql gt  SELECT BENCHMARK 10000000 FLOOR 1234567   7      ----------------------------------------    BENCHMARK 10000000 FLOOR 1234567   7      ----------------------------------------                                         0    ----------------------------------------  1 row in set  8 80 sec        NOTE  As pointed by Grbts  be aware of the behaviour of DIV 1 when used with non unsigned positive values

User · Answer

1 cent  no space b w CAST and  expression   i e   CAST columnName AS SIGNED

User · Answer

The CAST   function does not support the  official  data type  INT  in MySQL  it s not in the list of supported types  With MySQL   SIGNED   or  UNSIGNED   could be used instead   CAST columnName AS SIGNED    However  this seems to be MySQL-specific  not standardized   so it may not work with other databases  At least this document  Second Informal Review Draft  ISO IEC 9075 1992  Database does not list  SIGNED   UNSIGNED  in section 4 4  Numbers   But DECIMAL is both standardized and supported by MySQL  so the following should work for MySQL  tested  and other databases   CAST columnName AS DECIMAL 0     According to the MySQL docs      If the scale is 0  DECIMAL values contain no decimal point or   fractional part

User · Answer

There is an important difference between floor   and DIV 1  For negative numbers  they behave differently  DIV 1 returns the integer part  as cast as signed does   while floor x  returns  the largest integer value not greater than x   from the manual   So   select floor -1 1  results in -2  while select -1 1 div 1 results in -1

User · Answer

your can try this    SELECT columnName1  CAST columnName2 AS  SIGNED   FROM tableName

User · Answer

use this  mysql gt  SELECT TRUNCATE 223 69  0            gt  223   Here s a link

User · Answer

There s also ROUND   if your numbers don t necessarily always end with  00   ROUND 20 6  will give 21  and ROUND 20 4  will give 20

User · Answer

You could try the FLOOR function like this   SELECT FLOOR columnName   moreColumns  etc  FROM myTable  WHERE        You could also try the FORMAT function  provided you know the decimal places can be omitted   SELECT FORMAT columnName 0   moreColumns  etc  FROM myTable  WHERE        You could combine the two functions  SELECT FORMAT FLOOR columnName  0   moreColumns  etc  FROM myTable  WHERE

[mysql] CAST DECIMAL to INT

Examples related to mysql