Checking the decimal Length

taher1992

Hi there,

I am new to the community.

I was asked to check the length of the number of decimals after the decimal place

eg. 12.2123 - would give me 4

how can i check the length of the numbers after the decimal place as if i turn it into a v_w string i sometimes loose a zero at the end

eg. 12.20 - turns to 12.2 so when i check the length it gives me one

Tips and Tricks

Accepted answers

MarqueeCrew

If you use:

findstring("12345",'.')

your result will be -1.

IF 
     FindString([field],".") = -1     THEN 0
ELSE
     Length([Field]) - (1 + FindString([Field],".")
ENDIF

Will this work?

All comments

taher1992

Hi there,

I am new to the community.

I was asked to check the length of the number of decimals after the decimal place

eg. 12.2123 - would give me 4

how can i check the length of the numbers after the decimal place as if i turn it into a v_w string i sometimes loose a zero at the end

eg. 12.20 - turns to 12.2 so when i check the length it gives me one

thanks

jdunkerley79

Far too much of a brute force way but:

LENGTH(REGEX_REPLACE(TOSTRING([Field1],12),"^-?\d+.?(\d*?)0*$","$1"))

Will get length (up to 12 dp).

Basically, it converts to a string and then uses a regular expression to get just the part of the string after the decimal point and before the trailing zeros.

Expect a better maths based approach but that should work

MarqueeCrew

What is the length of the string?
What position is the decimal in?
What is the length minus the position of the decimal?

12.345

Length("12.345") = 6
1 + (FindString("12.345",'.') = 2 (zero based answer) ) = 3
6 minus 3 = 3

That's how I would approach this problem.

Cheers,

Mark

taher1992

The extra issue ( just realised) some of the data does not have a decimal. so that length should be zero. i will get an error if i use the find string on those fields

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703