The TRIM, UNPACK and DATE are popular functions in DB2 SQL.
TRIM is a scalar function in DB2. As of DB2 V11 we will see how this function will work.
Initial value in :HELLO1 = HELLO1
SELECT TRIM(:HELLO1), TRIM(TRAILING FROM :HELLO1) FROM SYSIBM.SYSDUMMY1
Result: HELLO1,HELLO1 – Both are same
In DB2 TRIM is having two arguments. One is trim constant, second one is 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 host variable. By default without giving first argument, it will remove spaces from both ends.
If second argument is NULL, we get NULL value.
Since, in any function, the basic rule is it should return a value. So, if second argument is NULL, then result will be NULL.
UNPACK – It is a row function in DB2
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’),
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 get null value.
Date function always return date from an expression.
DATE(timestamp1) => From the “timestamp1” it returns Date.
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.
- 3 Top things to consider while reviewing the SQL program
- Multiple nested Vs Correlated sub-query tricky top differences
- INSERT and SELECT in single SQL query top example for interviews
- SQL numeric functions project example queries
- Three top DB2 Index questions you need to know