CODEWITHSUNDEEP
sqldb2decimal
SovietFrontier
SovietFrontier

Reputation: 2247

DB2 SQL - Customize Decimal Points Based on Value

I am looking to mask decimal fields with custom decimal points. Here is an example as to what I am looking to do.

----------------------------------------------
-fieldName----fieldValue----result after mask-
----------------------------------------------
 505          2             5.05
 76812        5             .76812
 8185         3             8.185

All of these columns are stored as an INT or a DEC without any decimal points.

Maybe DB2 has something similar to the FORMAT() SqlServer function? That masks the value and doesn't do exactly what I would like but still, it's a start.

Thanks

Upvotes: 0

Views: 733

Answers (2)

jackic23
jackic23

Reputation: 139

Use built in math functions and divide by power of 10.

CAST(fieldName AS DOUBLE) / POWER(10, fieldValue)

If you are storing these numbers this way because the numbers are just too big then alternatively you can convert them to VARCHAR, then you can use SUBSTR().

Upvotes: 1

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

What about a maths solution?

 cast(fieldName as decimal(12,6)) / power(10, fieldvalue)

The cast is just to make sure it is a decimal field (the definition needs to be big enough for your data)

Upvotes: 1

Related Questions