Unpack Example 2: No Delimiter Separates Virtual Columns - Teradata Vantage

Input

The input table, ville_tempdata1, is like the input table for the previous example, except that no delimiter separates the virtual columns in the packed data. To enable the function to determine the virtual columns, the function call specifies the column lengths.

SQL Call

SELECT * FROM Unpack (
  ON ville_tempdata1
  USING
  TargetColumn ('packed_temp_data')
  OutputColumns ('city', 'state', 'temp_f')
  OutputDataTypes ('varchar', 'varchar', 'real')
  ColumnLength (9, 9, 4) 
  Regex ('(.*)')
  RegexSet (1)
  IgnoreInvalid ('true')
) AS dt ORDER BY sn;

Output