CODEWITHSUNDEEP
sqldecimal
Kristy
Kristy

Reputation: 23

How can I find values with more than 2 decimal places?

I am validating data and trying to find if there are any values in a single column (allowed_amount) with more than 2 decimal places (24.1145678, 234.444, -1234.09012).

with t1 as (
  select (allowed_amount - round(allowed_amount,2)) as ck
  from export_core_report_client_output
  where runid = '0c7c2d34-6cc3-43b0-ae4b-4bd8f4bddfb0'
) 
select min(ck) as min, max(ck) as max from t1

Upvotes: 2

Views: 1606

Answers (2)

Rishi
Rishi

Reputation: 313

You can use Charindex to do that, supposing the allowed_amount column is varchar or nvarchar

select len(substring(allowed_amount,charindex('.',allowed_amount)+1,len(allowed_amount))) from export_core_report_client_output

This will give you a count of decimal values after and then you can use the same statement in where clause to scrutinize like:

select len(substring(allowed_amount,charindex('.',allowed_amount)+1,len(allowed_amount))) from export_core_report_client_output where len(substring(allowed_amount,charindex('.',allowed_amount)+1,len(allowed_amount)))> 2

any questions fire up in the comments

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

One option would be to use this formula:

SELECT
    num,
    CASE WHEN 100*num - CAST(100*num AS int) > 0 THEN 'yes' ELSE 'no' END AS field
FROM yourTable;

Demo

For example, for the value 24.1234, the above formula computes:

2412.34 - 2412 = 0.34 > 0

But for 24.12, we get:

2412 - 2412 = 0

Upvotes: 3

Related Questions