CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Abenamor
Abenamor

Reputation: 318

SQL to get all number that have more than 3 digits after the decimal point

I would like to get all number that have more than 3 digits after the decimal point.

For example, I have a table called ARTICLE that have two columns: name (varchar type) and price (number).

I would like to get all the record stored in table ARTICLE where the price column value have more than 3 numbers after the decimal point.

For example, ARTICLE.price value equal to 12.9584 or 45.874521 will be returned since they have more than 3 numbers after the decimal point.

How could I achieve this please?

I tried this request but it is not correct:

select name, price 
from ARTICLE 
where length(TO_CHAR(price)) > 7;

Thanks

Upvotes: 2

Views: 4853

Answers (4)

Krzysztof Kaszkowiak
Krzysztof Kaszkowiak

Reputation: 896

You can operate on numbers only:

where trunc(price * 1000) - price * 1000 <> 0

Upvotes: 2

Marmite Bomber
Marmite Bomber

Reputation: 21075

Use ROUND function - you are looking for number that don't equal a rounded value to three digits.

Example - you test data

select round(2/3,rownum) price from dual connect by level <= 5

     PRICE
----------
,7        
,67       
,667      
,6667     
,66667

Query to get numbers with more that 3 digits after the decimal point

with dt as (
select round(2/3,rownum) price from dual connect by level <= 5
)
select price from dt
where price != round(price,3)

     PRICE
----------
,6667     
,66667

Upvotes: 4

wolφi
wolφi

Reputation: 8361

Coming to a similar solution than @Krzysztof:

CREATE TABLE article (name VARCHAR2(10), price NUMBER);

INSERT INTO article VALUES ('a', 12.9584);
INSERT INTO article VALUES ('b', 45.874521);
INSERT INTO article VALUES ('c', 0.123);


SELECT * FROM article WHERE ABS(price - TRUNC(price,3)) > 0;

NAME    PRICE
   a    12,9584
   b    45,874521

Upvotes: 2

Taylor Tapio
Taylor Tapio

Reputation: 1

I don't have a table to test this on, but you might be able to use:

Select Name, Price
From Article
Where Price like '%#.###%'

This would say that there has to be at least 1 number before the decimal, and at least 3 after the decimal. More info on the wildcard characters below:

https://www.w3schools.com/sql/sql_wildcards.asp

Hopefully this helps!

Upvotes: -1

Related Questions