CODEWITHSUNDEEP
mysql
Drahcir
Drahcir

Reputation: 11982

CAST DECIMAL to INT

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

Upvotes: 96

Views: 256882

Answers (10)

David Strencsev
David Strencsev

Reputation: 1095

A more optimized way in 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

Speed of MySQL integer division Bar Chart (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.

Upvotes: 34

bbe
bbe

Reputation: 344

1 cent: no space b/w CAST and (expression). i.e., CAST(columnName AS SIGNED).

Upvotes: 0

panda
panda

Reputation: 466

use this

mysql> SELECT TRUNCATE(223.69, 0);
        > 223

Here's a link

Upvotes: 15

basic6
basic6

Reputation: 3811

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.

Upvotes: 19

mshabab
mshabab

Reputation: 51

your can try this :

SELECT columnName1, CAST(columnName2 AS  SIGNED ) FROM tableName

Upvotes: 5

Jamie Brown
Jamie Brown

Reputation: 1053

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.

Upvotes: 3

Grbts
Grbts

Reputation: 131

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

Upvotes: 11

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

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

Upvotes: 149

ajreal
ajreal

Reputation: 47331

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)

Upvotes: 1

fredley
fredley

Reputation: 33941

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

Upvotes: 24

Related Questions