Common functions in DB2 (constantly updated in ...)

Common functions in DB2:

Function name	Explanation of the function numbers	Example
SUBSTR (EXP1,EXP2) SUBSTR (EXP1,EXP2,EXP3)	Returns the EXP1 string starting at EXP2, with a substring length of EXP3	SELECT SUBSTR (' CDNJFDJFJD ', 5) from Bsempms; SELECT SUBSTR (' CDNJFDJFJD ', 5,2) from Bsempms;
ROUND (EXP1,EXP2)	Returns the rounded value starting at the EXP2 position to the right of the EXP1 decimal point.	SELECT ROUND (2345.6789,2) from Bsempms
REPLACE (EXP1,EXP2,EXP3)	Replace all the EXP2 in EXP1 with EXP3	SELECT CHAR (REPLACE (' romandd ', ' NDD ', ' CCB '), ten) from Bsempms
REPEAT (EXP1,EXP2)	Returns the string after EXP1 repeats EXP2 times	SELECT CHAR (REPEAT (' REPEAT ', 3), +) from Bsempms
RAND ()	Returns the random floating-point number between 0 and 1	SELECT RAND () from Bsempms
Posstr (EXP1,EXP2)	Returns the position of the EXP2 in EXP1	SELECT posstr (' abcdefgh ', ' D ') from Bsempms
Nullif (EXP1,EXP2)	NULL if EXP1=EXP2, otherwise EXP1
COALESCE (Col_1, ")	field Null value substitution function	1. The input parameter is a character type, and is allowed to be empty, you can use COALESCE (InputParameter, ') to convert null to '; 2. Input type is integral type, and allowed to be empty, can use COALESCE (inputparameter,0), turn idling into 0; 3. The input type is integer and is non-null and does not require the use of the COALESCE function to directly use is NULL for a nonempty judgment.
LENGTH (EXP)	Get the length of the string exp	Values length (' Test '); --Output 4
CAST (EXP1 as EXP2)	Type conversion function: Convert EXP1 to EXP2 type	1. Convert characters to integers: cast (' 2 ' As Integer); 2. Convert to String: Cast (current date as char (20)); 3. Convert to floating point number: Cast (' 22.02 ' as Decimal (4,2))
LTRIM (EXP) RTRIM (EXP)	DB2 provides the LTrim function and the RTrim function, but does not provide the trim function, if you want to remove the spaces at both ends of the character, I'm sorry, I have to call it in LTrim (RTrim ()).	1. Remove left space: LTRIM (' ABC '); 2. Remove the right space: LTRIM (' ABC '); 3. Remove the left and right spaces: LTRIM (RTRIM (' ABC '));