CAST DECIMAL to INT
MysqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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 ...
Solution 2 - Mysql
A more optimized way in [tag: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.
Solution 3 - Mysql
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
Solution 4 - Mysql
Solution 5 - Mysql
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.
Solution 6 - Mysql
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
Solution 7 - Mysql
your can try this :
SELECT columnName1, CAST(columnName2 AS SIGNED ) FROM tableName
Solution 8 - Mysql
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.
Solution 9 - Mysql
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)
Solution 10 - Mysql
1 cent: no space b/w CAST and (expression). i.e., CAST(columnName AS SIGNED).