The purpose of the TRIM function is you can trim a portion of the input string. The purpose of the PACK function is it converts the input string to a variable binary string. The purpose of the UNPACK function is opposite to PACK. The purpose of the DATE function is to convert the input Date to DDMMMYYYY format.
TRIM is a scalar function. In DB2 v11, we will see here how this function works.
Initial value in :HELLO1 = HELLO1
SELECT TRIM(:HELLO1), TRIM (TRAILING FROM :HELLO1) FROM SYSIBM.SYSDUMMY1;
Result: HELLO1 and HELLO1 – Both are same
In DB2, TRIM is having two arguments -trim constant and string expression.
Valid first argument values:
- TRAILING FROM
- LEADING FROM
- L ‘0’
- R ‘ ‘
- B ‘ ‘
The second argument is an expression. It can be any string or a host variable. By default without giving the first argument, it will remove spaces from both ends. If the second argument is NULL, we get a NULL value.
Since, in any function, the basic rule is it should return a value. So, if the second argument is NULL, then the result will be NULL.
UNPACK and PACK Functions.
The UNPACK function returns a row of values that are derived from unpacking the input binary string. It is used to unpack a string that was encoded according to the PACK function.
One excellent example on PACK and UNPACK is as follows:
SET :udf_result = PACK(CCSID 1208,’Alina’, DATE(‘1977-08-01’), DOUBLE(0.5));
Let us assume that, we have create an UDF with the above query, with a name “myfirstudf”.
How to use UNPACK
SELECT UNPACK(myfirstudf(C1)).* AS(Name VARCHAR(40) CCSID UNICODE, DOB DATE, Score DOUBLE) FROM T1;
The result from UNPACK should be valid value. Never we will get null value.
Date function always return date from an expression.
DATE(timestamp1) => From the “timestamp1” it returns Date. timestamp1=1988-12-25-17.12.30.000000
Result= 25 DEC 1988
** Interview Question
Value in DATECOL1 Char(07) = ‘1989061’
The result will be 02 MAR 1989. This is internal representation value.
Note: Even though the size of the variable is CHAR(07), it considers as valid date, and it gives internal representation value.