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

Answer 1

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 ...  

Answer 2

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

(graphic from Roland Bouman's blog)

mysql> SELECT BENCHMARK(10000000,1234567 DIV 7) ; +-----------------------------------+ | BENCHMARK(10000000,1234567 DIV 7) | +-----------------------------------+ |                                 0 | +-----------------------------------+ 1 row in set (0.83 sec)  mysql> SELECT BENCHMARK(10000000,1234567 / 7) ; +---------------------------------+ | BENCHMARK(10000000,1234567 / 7) | +---------------------------------+ |                               0 | +---------------------------------+ 1 row in set (7.26 sec)  mysql> 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.