DB2 DATE, PACK, UNPACK, and TRIM Functions

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 Function.

TRIM is a scalar function. In DB2 v11, we will see here how this function works.

Example:

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 ‘ ‘

Second argument:

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.

**Interview Question

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.

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

DATE(DATECOl1)

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.

Also Read

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.