Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query to find records with more than 2 decimal places

727688Dec 28 2010 — edited Dec 28 2010
I have written the below query to find records with more than 2 decimal places, but it is returning records with decimal places 1 & 2. 
The datatype of the AMT column is NUMBER (without any precision).

SELECT amt  FROM amount_table
 WHERE substr(amt, instr(amt, '.')) LIKE '.%'
       AND length(substr(amt, instr(amt, '.') + 1)) > 2
Output:-

AMT

*41591.1*
*275684.82*
*64491.59*
*3320.01*
*6273.68*
*27814.18*
*30326.79*
131.8413635
162.5352898
208.5203816
8863.314632
22551.27856
74.716992
890.0158441
2622.299682
831.6683841
*1743.14*
2328.195877
3132.453438
5159.827334
3.236234727
37.784
Thanks
This post has been answered by Frank Kulash on Dec 28 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 25 2011
Added on Dec 28 2010
9 comments
26,040 views