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

Question

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

http://stackoverflow.com/questions/1860542/what-would-be-a-reliable-way-to-get-fractional-value-from-a-number — Pekka, Sep 25 '16 at 17:24

score 2 · Accepted Answer · answered Sep 25 '16 at 17:23

2

Here's one option with cast:

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

SQL Fiddle Demo

answered Sep 25 '16 at 17:23

sgeddes

62,311
6
61
83

Or `floor(number) <> number` – Paul Spiegel Sep 25 '16 at 17:29

score 1 · Answer 2 · answered Sep 25 '16 at 17:28

1

Here is a another way using % opertaor

select *
from Youtable
where `number` % 1 <> 0

SQL FIDDEL DEMO

answered Sep 25 '16 at 17:28

Pரதீப்

91,748
19
131
172

score 0 · Answer 3 · edited Jul 03 '23 at 17:09

0

solution:

SELECT * FROM sample where number like '%.%';

edited Jul 03 '23 at 17:09

Pravash Panigrahi

701
2
7
21

answered Jul 01 '23 at 10:38

vasim shah

1

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

3 Answers3