Introduction and use of DB2 numeric Functions

Introduction to DB2 numeric functions and usage learning a database, we started learning from its internal functions. These internal functions
Data is of great help to write SQL statements. This article does not cover all of them, but it can play a guiding role. For details, refer to the official website. 1. functions that take the absolute value: abs, absval parameter quantity: 1 parameter type: smallint, integer, bigint data type; can also be null, the return value is also null. Db2 => select abs (-123), abs (null), abs ('-000000') from sysibm. sysdummy11 2 3 ------------- ------------------------------------ selected 1 record for limit 100-123. Db2 => select abs (NaN) | ''| abs (sNaN) |'' | abs (Infinity) from sysibm. sysdummy11 ---------------------- nan snan infinity 1 record selected. 2. rounded up: ceil, ceilingdb2 => select ceil (123.89), ceiling (123.49) from sysibm. sysdummy11 2 ------- 124. 124. 1 records selected. If the ceil function parameter is a string consisting of a number, such as '192. 45', this function can convert it to a floating point number and perform the Integer Operation. Db2 => select ceil (123.45), ceil ('192. 45') from sysibm. sysdummy11 2 ------- -------------------------------------- 123. 124 1 record selected. 3. Take down INTEGER: floor parameter count: 1 parameter type: integer and floating point number. It can also be null. the return value is null. Db2 => select floor (123.56), floor (100), floor (-123.99) from sysibm. sysdummy11 2 3 ------- ----------- ------- 123. 100-124. 1 records are selected. Db2 => select floor (null) from sysibm. sysdummy11 ---------------------------------------------1 record selected. 4. Evaluate the n power of M: power (M, n) Return Value: integer: if both parameters are integer or smallintbigint: if one is of the bigint type, the other is bigint, integer, or smallint. Double: if both parameters are floating point numbers. Db2 => select power (3.2, 2.1), power (), power () from sysibm. sysdummy11 2 3 ------------------------ ----------- + 1.15031015682351E + 001 9 1 1 record selected. 5. Random Number: Return Value of the rand (m) function: return a floating point number between 0 and 1. If the parameter is null, the result is also null. Parameter: the number of smallint or integer in the range of 0-2, 147483647. It is called a seed. Db2 => select rand (), rand (500), rand (null) from sysibm. sysdummy11 2 3 -------------------------------------------------------------- + 2.43232520521_8e-001 + 5.09964293343913E-002-1 records have been selected. Generate a random number of 0-1: select rand () from sysibm. sysdummy1 generates a random integer between 0 and n: select cast (rand () * n as int) from sysibm. sysdummy1db2 => values (cast (rand () * 10 as int) 1 ----------- 5 1 record selected. Db2 => values (cast (rand () * 10 as int) 1 ----------- 1 1 record selected. Generate a random INTEGER (m> n) for n-m: select n + cast (rand () * (m-n) as int) from sysibm. sysdummy1db2 => select 5 + cast (rand () * (10-5) as int) from sysibm. sysdummy11 ----------- 9 1 record selected. Db2 => select 5 + cast (rand () * (10-5) as int) from sysibm. sysdummy11 ----------- 7 1 record selected. You can use the rand () function and fetch frist to randomly retrieve rows from the table and obtain n rows of data from the table (in fetch first, n must be an integer greater than or equal to 1; otherwise, an error is returned ): select * from tb order by rand () fetch first n rows onlydb2 => select B. * from (select row_number () over (),. empno,
A. salary from employee a) B order by rand () fetch first 5 rows only1 empno salary ------------------ ------ ----------- 19 000210 68270.00 20 000220 49840.00 11 000130 73800.00 000320 39950.00 12 000140 68420.00 5 records are selected. Db2 => select B. * from (select row_number () over (), a. empno,
A. salary from employee a) B order by rand () fetch first 5 rows only1 empno salary ------------------ ------ ------------- 18 000200 57740.00 13 000150 55280.00 17 000190 15 50450.00 33 000170 44680.00 5 records are selected. 6. Rounding: round (m, n) parameter: m indicates a numerical value, integer, or floating point number. Positive and Negative Numbers can all be carried to the ceil () Direction. N indicates the number of carry places, an integer indicates the number after the decimal point, and a negative number indicates the number of integer parts. It is of the integer type. Instance: db2 => values (round (873.726, 2), round (873.726, 1), round (873.726, 0), round (873.726,-1), round (873.726, -2), round (873.726,-3) 1 2 3 4 5 6 --------- 873.730 873.700 874.000 870.000 900.000 1 record selected. Db2 => values (round (-745.5,-2), round (-745.5,-1) 1 2 --------700.0-750.0 1 record selected. 7. convert a value to a string in the form of: digits () function parameter: smallint, integer, bigint, decimal; char or varchar numeric string. Before performing this function operation,
The value of the string is implicitly converted to decimal ). The parameter can be null, And the return value is null. Return Value: return the string format of the numeric value. If the length is not long, it is automatically filled with 0 before the character. Returns a fixed-length string. If the parameter is a floating-point number, the value after the decimal point is also included. If the length is insufficient, add 0 at the front of the integer. The following is an instance. The structure of the employee table is as follows. The length of the last three fields is 9 and the number of decimal places is 2: db2 => describe table employee data type column name mode data type name long decimal place NULL ----------- ---------- ------ empno sysibm character 6 0 No firstnme sysibm varchar 12 0 No midinit sysibm character 1 0 is lastname sysibm varchar 15 0 No workdept sysibm character 3 0 is phoneno sysibm character 4 0 is hiredate sysibm date 4 0 is JOB SYSIBM CHARA CTER 8 0 is edlevel sysibm smallint 2 0 no sex sysibm character 1 0 is birthdate sysibm date 4 0 is salary sysibm decimal 9 2 is bonus sysibm decimal 9 2 is comm sysibm decimal 9 2 is 14 records are selected. The conversion of the digits function is as follows: select salary, digits (salary), bonus, digits (bonus), comm, digits (comm) from employee fetch first 5 rows only SALARY 2 BONUS 4 COMM 6 ----------- --------- 152750.00 015275000 1000.00 000100000 4220.00 000422000 94250.00 009425000 800.00 000080000 3300.00 000330000 98250.00 009825000 800.00 000080000 3060.00 000080000 3214.00 000321400 72250.00 007225000 500.00 000050000 2580.00 000258000 5 Records selected. 8. convert to a value: to_number (string, format). This function is the alias of decfloat_format. Parameter: string is a numeric string, for example, '-100.1' or '123. Format is the numeric format after conversion. The format is 0 or 9: Each number represents a character. MI: For a negative number, move the negative sign (-) at the end to the beginning. For a positive number, add a sign (+) or space before it. S: for example, in the format of MIPR, the number contained in a pair of angle brackets (<>) is considered as a negative number, and a negative sign (-) is added before it after conversion (-). $: The conversion string must be preceded by the $ symbol .,: Comma, numeric separator.: Point, decimal point. Db2 => values to_number ('2017. 001 ') + 0.011 ------------------------------------------ 123.011 db2 => values to_number ('2017. 456 ', 000. 000 ') 1 ---------------------------------------- 123.456 db2 => values to_number ('2017. 456 ', 000. 000 ') 1 ---------------------------------------- 123.456db2 => values to_number ('2017. 100', '123. 999MI ') 1 -------------------------------------------- 987.123 db2 => values _ Number ('<123.456>', 000. 000PR ') 1 bytes-123.456 db2 => values to_number (' $123,456.78 ',' $000,000.00 ') 1 ------------------------------------------ 123456.78 9. Numeric truncation function: trunc (m, n) or truncate (m, n) parameter: m is a numeric, integer, or floating point number. n is the truncation length. If n is greater than 0, the fractional part is truncated. If n is less than 0, return Value of the integer part: for the specified value m, n length values are truncated. Db2 => values (trunc (873.726, 2), trunc (873.726, 1), trunc (873.726, 0) Do you want to execute the above command? (Y/n) y1 2 3 -------- 873.720 873.700 873.000 1 record selected. Db2 => values (trunc (873.726,-1), trunc (873.726,-2), trunc (873.726, 2), trunc (873.726,-3 )) do you want to execute the above command? (Y/n) y1 2 3 4 -------- 870.000 800.000 873.720 0.000 1 record selected. 10. convert to a floating point: decfloat (m, 16 | 34) parameter: m can be a numeric or string (a string consisting of numeric values, for example, '123. 98 ') Return Value: Convert m to a floating point number with a length of 16 or 34. The default value is 34. db2 => values (decfloat (123.45), decfloat ('-000000', 16) + 10) 1 2 ---------------------------------------- ------------------------ 123.45-123.45 1 records have been selected. 11. numeric comparison function: compare_defloat (a, B) parameter: Both parameters are decfloat (34) floating point numbers; otherwise, they are automatically converted to decfloat (34 ). Returned value: 0: a = b1: 2: a> b3: there is no direct sequential relationship between a and B db2 => values (compare_decfloat (decfloat (1.5), decfloat (1.5 )),
Compare_decfloat (decfloat (1.5), decfloat (1.50) 1 2 ------ 0 2 1 record selected. Db2 => values (compare_decfloat (decfloat (1.770), decfloat (1.77), compare _
Decfloat (NaN), decfloat (1.2) 1 2 ------ 1 3 1 records selected. The following examples are available on the official website: COMPARE_DECFLOAT (DECFLOAT (2.17), DECFLOAT (2.17) = 0 COMPARE_DECFLOAT (DECFLOAT (2.17), DECFLOAT (2.170 )) = 2 COMPARE_DECFLOAT (DECFLOAT (2.170), DECFLOAT (2.17) = 1 COMPARE_DECFLOAT (2.17), DECFLOAT (0.0) = 2 COMPARE_DECFLOAT (INFINITY, INFINITY) = 0 COMPARE_DECFLOAT (INFINITY,-INFINITY) = 2 COMPARE_DECFLOAT (DECFLOAT (-2), INFINITY) = 1 COMPARE_DECFLOAT (NAN, NAN) = 3 COMPARE_DECFLOAT (DECFLOAT (-0.1 ), SNAN) = 3 12. normalize_decfloat (decfloat_number) parameters: smallint, integer, real, double,
Or decimal (p, s) (p <= 16) type; bigint or decimal (p, s) (p> 16) will be converted to decfloat (34 ). Return Value: convert a value to a decimal number multiplied by the n power of 10,
For example, 1200 is converted to 1.2E3. Db2 => values (normalize_decfloat (decfloat (-1200), normalize _
Decfloat (1.2000) 1 2 ---------------------------------------------------------------------------1.2E + 3 1.2 records have been selected. The following examples are available on the official website: NORMALIZE_DECFLOAT (DECFLOAT (2.1) = 2.1NORMALIZE _ DECFLOAT (-2.0) =-2NORMALIZE_DECFLOAT (DECFLOAT (1.200 )) = 1.2NORMALIZE _ DECFLOAT (-120) =-1.2E + 2NORMALIZE_DECFLOAT (DECFLOAT (120.00) = 1.2E + lower (DECFLOAT (0.00) = lower (-NAN) =-NaN NORMALIZE_DECFLOAT (-INFINITY) =-Infinity there are many system functions, such as mathematical functions, sin, cos, tan, exp, etc. For details, refer to the official website: http://pic.dhe.ibm.com/infocenter/
Db2luw/v9r7/index. jsp location: Database basics-> SQL-> Functions