DB2 decimal part of a floating point number

DB2 takes the fractional part of the floating point number. The simplest and quickest way is to use the floor function or the cast function to obtain the integer part, then, subtract the integer from the original number: [SQL] select 123.556-floor (123.556) from qsys2/qsqptabl .... + .... 1 .... +... numeric Expression. 556 * End of data * DB2 V10.1 for luw: [SQL] 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 used for mathematical operation. Calculate www.2cto.com 2. Fixed floating point length and fixed decimal places, but the actual storage value is uncertain. for example: DB2 for I (AS400) query 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: [SQL] 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 or use the above data. The following results are obtained: [SQL] 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: [SQL] 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 3. If the floating point length and number of decimal places are fixed, the actual storage value is fixed. For example, if the integer length is 3 and the number of decimal places is 2, the following processing can be performed: query on www.2cto.com DB2 for I (AS400): [SQL] select char (123.99), right (trim (char (123.99), 2) from qsys2/qsqptabl .... + .... 1 .... + .... 2 .... CHAR (123.99) RIGHT 123.99 99 ********* End of data ******** DB2 V10.1 for windows: [SQL] select char (123.99), right (trim (char (123.99), 2) from sysibm. sysdummy1 1 2 --------- ------- 123. 99 note: from the two DB2 database versions, after the value is converted to a string, the length is 1 longer than the original character. Therefore, when receiving a string, remove the Left and Right spaces (trim ). The value obtained here is a string. If other computation or comparison is required, convert it to a number. Use display conversion to avoid implicit conversion in DB2.