Unpack Function Example | Teradata Vantage - Example: Delimiter Separates Virtual Columns - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

Unpack Input

The input table, ville_tempdata, is a collection of temperature readings for two cities, Nashville and Knoxville, in the state of Tennessee. In the column of packed data, the delimiter comma (,) separates the virtual columns. The last row contains invalid data.

ville_tempdata
sn packed_temp_data
10 Nashville,Tennessee,35.1
11 Nashville,Tennessee,36.2
12 Nashville,Tennessee,34.5
13 Nashville,Tennessee,33.6
14 Nashville,Tennessee,33.1
15 Nashville,Tennessee,33.2
16 Nashville,Tennessee,32.8
17 Nashville,Tennessee,32.4
18 Nashville,Tennessee,32.2
19 Nashville,Tennessee,32.4
20 Thisisbaddata

Unpack SQL Call with Delimiter Separates Virtual Columns

Because comma is the default delimiter, the Delimiter syntax element is optional.

SELECT * FROM Unpack (
  ON ville_tempdata AS InputTable
  USING
  TargetColumn ('packed_temp_data')
  OutputColumns ('city', 'state', 'temp_f')
  OutputDataTypes ('varchar', 'varchar', 'real')
  Delimiter (',') 
  Regex ('(.*)')
  RegexSet (1)
  IgnoreInvalid ('true')
  Accumulate ('sn')
) AS dt ORDER BY sn;

Unpack Output with Delimiter Separates Virtual Columns

Because of IgnoreInvalid ('true'), the function did not fail when it encountered the row with invalid data, but it did not output that row.

city state temp_f sn
Nashville Tennessee 3.51000000000000E 001 10
Nashville Tennessee 3.62000000000000E 001 11
Nashville Tennessee 3.45000000000000E 001 12
Nashville Tennessee 3.36000000000000E 001 13
Nashville Tennessee 3.31000000000000E 001 14
Nashville Tennessee 3.32000000000000E 001 15
Nashville Tennessee 3.28000000000000E 001 16
Nashville Tennessee 3.24000000000000E 001 17
Nashville Tennessee 3.22000000000000E 001 18
Nashville Tennessee 3.24000000000000E 001 19