# Way to show items where more than 5 decimal places occur?

2075 views
-2

I am trying to filter out some query results to where it only shows items with 6 decimal places. I don't need it to round up or add 0's to the answer, just filter out anything that is 5 decimal places or below. My current query looks like this: (ex. if item is 199.54215 i dont want to see it but if it is 145.253146 i need it returned)

``````select
TRA_CODPLANTA,
TRA_WO,
TRA_IMASTER,
tra_codtipotransaccion,
tra_Correlativo,
TRA_INGRESOFECHA,
from mw_tra_transaccion
and substring(tra_imaster,1,2) not in ('CP','SG','PI','MR')
and TRA_CODPLANTA not in ('4Q' , '5C' , '5V' , '8H' , '7W' , 'BD', 'DP')
AND tra_INGRESOFECHA > @from_date
``````

Any assistance would be greatly appreciated!

You could convert to varchar, then do a SUBSTRING starting from the '.' and if the LEN is greater than 5, it is a number with at least 6 decimal places. OR you could take the LEN - the CHARINDEX of the period, which would probably be easier

That's what `where FLOOR (Tra_cantidadparcial*100000) !=tra_cantidadparcial*100000` already accomplishes, inasmuch as it considers non-zero digits only, and not literally the number of decimals used in storage. If it's not working, take a closer look at your source data (it won't necessarily work correctly for `FLOAT` data, for starters).

12

Here is an example using a conversion to varchar and using the LEN - the CHARINDEX of the decimal point:

``````DECLARE @temp DECIMAL(20, 10) = 123.4565432135

IF LEN(CAST(@temp AS varchar(20))) - CHARINDEX(CAST(@temp AS varchar(20)), '.', 0) > 5
SELECT 1
ELSE
SELECT 0
``````

posted this