0

Is there a way through an SQL query or similar to find values that exceed 2 decimal points? Something like

SELECT * FROM TableName WHERE the decimels > 2

Example image below highlights 2 values are need to be fixed, but im not sure how to select these values only.

enter image description here

We only display 2 decimals on screens so anything over that is not rounded. We do fix them up as we see them but there are thousands of values in the Database. Being able to easily identify them would really help.

Thanks

Rob
  • 99
  • 1
  • 3
  • 12
  • Hint: `ROUND(1429,0) = 1429`, `ROUND(1453.33,0) <> 1453`. – Jeroen Mostert Sep 06 '21 at 16:41
  • For your reference : https://stackoverflow.com/questions/32462644/select-numbers-with-more-than-4-decimal-places – Akash R Sep 06 '21 at 16:44
  • 1
    Please do not post images of code/errors/data. Instead post the code/errors/data as text in a code block. See [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). People with screen readers cannot know what you mean, because they cannot see the content of the image. – Scratte Sep 06 '21 at 19:29
  • [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Sep 06 '21 at 20:23

2 Answers2

0

you can count decimals with floor(log10(reverse(abs(Price)+1)))+1:

select * from TableName
where floor(log10(reverse(abs(Price)+1)))+1 > 2;

here is a test with your numbers: SQL Fiddle
and here the reference for decimal count

Ísis Santos Costa
  • 371
  • 1
  • 3
  • 9
0

This function could be help you

select REVERSE(ROUND(REVERSE(3.2300),1))

tty0
  • 56
  • 3