CODEWITHSUNDEEP
sqlmysql
yvoyer
yvoyer

Reputation: 7516

Mysql: How to get every rows that have more than a certain number of decimal after the dot

I have a table that contains float values.

table

+   id   |  value  |
+--------|---------|
+   1    | 19.22   |
+   2    | 32.333  |
+   3    | 1.2332  |
+   4    | 0.22334 |
+   5    | 4.55    |

I want to extract every row that contains more than 3 decimal after the dot.

The result I would expect is:

+   id   |  value  |
+--------|---------|
+   2    | 32.333  |
+   3    | 1.2332  |
+   4    | 0.22334 |

Upvotes: 6

Views: 7700

Answers (7)

jeanmi1229
jeanmi1229

Reputation: 1

SQL Server: To extract values with more than 3 decimal places from the "quantity" column in a SQL table with the "quantity" column using float data type, you can use the following query:

SELECT *
FROM `table`
WHERE ROUND(quantity, 3) <> quantity;

Upvotes: 0

Surendra Sharma
Surendra Sharma

Reputation: 11

SELECT * FROM table t WHERE floor(t.columnname)!=t.columnname

Upvotes: 1

JudgeDredd
JudgeDredd

Reputation: 61

This worked for me:

SELECT  *
FROM table
WHERE column <> ROUND (column,2)

or:

SELECT  *
FROM table
WHERE column <> CAST (column AS DECIMAL(36,2))

Upvotes: 6

Mo Kelly
Mo Kelly

Reputation: 31

This worked for me 

SELECT * 
FROM `table` 
WHERE LENGTH(SUBSTR(`value`,INSTR(`value`,"."))) >3

Counting the decimal .01 is 3, .011 is 4

Upvotes: 3

Remus
Remus

Reputation: 1443

Try this:

select value, LEN(value) - CHARINDEX('.', value) as DecimalCount from Table
where LEN(value) - CHARINDEX('.', value) > 2

NOTE: I'm not using mySQL, I have MSSQL. let me know if this works for you - just noticed you asked for a mySQL solution.

UPDATE: Felt bad for answering for the wrong platform. A bit of research got me this for mySQL:

select * from Table
where LENGTH(SUBSTRING_INDEX(value, '.', -1)) > 2

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332691

This regex (MySQL 5.0+) worked for me, based on the data you provided:

SELECT t.* 
  FROM YOUR_TABLE t 
 WHERE t.`value` REGEXP '[0-9]+.[0-9][0-9][0-9]+'

Reference:

Upvotes: 4

dotariel
dotariel

Reputation: 1604

Cast the value column as a varchar and use string comparison.

Upvotes: 6

Related Questions