DB2 decimal part of a floating point number

For various DB2 versions, use the floor function or cast function to take the integer part, and then subtract the integer part from the original number:

For various DB2 versions, use the floor function or cast function to take the integer part, and then subtract the integer part from the original number:

I. Simplest and quickest method

For various DB2 versions, use the floor function or cast function to take the integer part, and then subtract the integer part from the original number:

DB2 for I:

Select 123.556-floor (123.556) from qsys2/qsqptabl
... +... 1... +...
Numeric Expression
. 556
* ******* End of data ********

DB2 V10.1 for luw:

Select 123.99-cast (123.99 as int), 100.1-floor (100.1) from sysibm. sysdummy1
1 2
------------------------
0.99 0.1

Note: The data obtained here is a floating point number, which can be calculated in mathematics.

2. Fixed floating point numbers and decimal places, but the actual storage values are uncertain, as follows:

Queries on DB2 for I (AS400)

1. Use locate to locate the decimal point after conversion to a string, and then use this decimal point to take the integer and decimal part:

Select substr (char (123.99), locate ('.', char (123.99) + 1), char (123.99)
From qsys2/qsqptabl
... +... 1... +... 2 ....
Substr char (123.99)
99 123.99
* ******* End of data ********

2. Use the position or posstr function to locate the decimal point

Use the above data to get the following results:

Select char (123.99 ),
Substr (char (123.99), position ('.' in char (123.99) + 1 ),
Substr (char (123.99), posstr (char (123.99), '.') + 1)
From qsys2/qsqptabl
... +... 1... +... 2... +... 3...
CHAR (123.99) SUBSTR
123.99 99 99
* ******* End of data ********

DB2 V10.1 for luw queries:

Select char (123.99 ),
Substr (char (123.99 ),
Position ('.' in char (123.99) using octets) + 1 ),
Substr (char (123.99), posstr (char (123.99), '.') + 1)
From sysibm. sysdummy1
1 2 3
---------------------
123.99 99 99