UNPACK

>>-UNPACK--(--expression--)------------------------------------><

The schema is SYSIBM.

expression

An expression that returns the string value to be unpacked. The expression must be a binary string that is not a BLOB and that is not null. The format of the binary string must match the one that is produced by the PACK function.

The UNPACK function can only be specified in the SELECT list.

The result of the function is a row of fields corresponding to the data elements that were encoded in the input packed string. The result is not null.

Example 1: Assume that a user-defined function named myUDF returns a VARBINARY result. The body of the function includes the following invocation of the PACK function to pack some data into a binary string:

  SET :udf_result = PACK(CCSID 1208, 'Alina', DATE('1977-08-01'),
                        DOUBLE(0.5));

The following SELECT statement unpacks the result of the myUDF function and returns a row of individual column values:

  SELECT UNPACK(myUDF(C1)).* AS(Name VARCHAR(40) CCSID UNICODE,
                               DOB DATE,
                               Score DOUBLE)
    FROM T1;

The use of ".*" indicates that the result of the UNPACK function should be flattened into a list of result column values. When the UNPACK function is used in a select clause, an AS clause is specified to provide the names and data types for the resulting values.

Example 2: Assume that a user-defined function UDF_SCORE returns a VARBINARY result. The PACK function is invoked to return a binary string in which the column values of table T1 are encoded and packed. The UNPACK function returns the individual data values for a row with column names ID, SCORE, and CONF:

  SELECT T1.C1, T1.C2, T1.C3, T1.C4,
         UNPACK( UDF_SCORE( PACK(CCSID 1208, T1.C1, T1.C2, T1.C3)) ).*
                AS (ID INT, SCORE DOUBLE, CONF DOUBLE)
    FROM T1;

SELECT UNPACK(myUDF(C1)).* AS(MSG VARCHAR(10) CCSID UNICODE,
 DOB DATE,
 NAME VARCHAR(20) CCSID EBCDIC)
 FROM T1;