CODEWITHSUNDEEP
sql
Ben
Ben

Reputation: 71

How to find values with certain number of decimal places using SQL?

I'm trying to figure out a way, using SQL, to query for values that go out to, say, 5 or more decimal places. In other words, I want to see only results that have 5+ decimal places (e.g. 45.324754) - the numbers before the decimal are irrelevant, however, I still need to see the full number. Is this possible? Any help if appreciated.

Upvotes: 0

Views: 12325

Answers (6)

PravinH
PravinH

Reputation: 1

You can use below decode statement to identify maximum decimal present in database table 

SELECT max(decode(INSTR(val,'.'), 0, 0, LENGTH(SUBSTR(val,INSTR(val,'.')+1)))) max_decimal 
FROM tablename A;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

select val
from tablename
where length(substr(val,instr(val, '.')+1)) > 5

This is a way to do it in oracle using substr and instr

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32230

On SQL Server, you can specify:

SELECT *
FROM Table
WHERE Value <> ROUND(Value,4,1);

For an ANSI method, you can use:

SELECT *
FROM Table
WHERE Value <> CAST(Value*100000.0 AS INT) / 100000.0;

Although this method might cause an overflow if you're working with large numbers.

Upvotes: 1

BJones
BJones

Reputation: 2460

This worked for me in SQL Server:

SELECT * 
    FROM YourTable
    WHERE YourValue LIKE '%._____%';

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 454020

I imagine most DBMSs have a round function

SELECT * 
FROM YourTable 
WHERE YourCol <> ROUND(YourCol,4)

Upvotes: 0

mwigdahl
mwigdahl

Reputation: 16588

Assuming your DBMS supports FLOOR and your datatype conversion model supports this multiplication, you can do this:

SELECT *
FROM Table
WHERE FLOOR(Num*100000)!=Num*100000

This has the advantage of not requiring a conversion to a string datatype.

Upvotes: 2

Related Questions