zono zono - 2 months ago 9
MySQL Question

MySQL: How can I get only decimal value from double type column?

CREATE TABLE `sample` (
`number` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


mysql> select * from sample
+-------------------+
| number |
+-------------------+
| 1 |
| 2 |
| 3.5 |
| 4.5 |
| 0.1 |
+-------------------+


How can I get only the three decimal numbers?

+-------------------+
| number |
+-------------------+
| 3.5 |
| 4.5 |
| 0.1 |
+-------------------+

Answer

Here's one option with cast:

select * 
from sample
where cast(number as unsigned) <> number