Optional Syntax Elements for Unpack - 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™
ON clause
Accepts the InputTable clause.
Delimiter
Specifies the delimiter, a single Unicode character in Normalization Form C (NFC), that separates the virtual columns in the packed data. The delimiter is case-sensitive.
Do not specify both this and ColumnLength. If the virtual columns are separated by a delimiter, specify the delimiter with this syntax element; otherwise, specify the ColumnLength syntax element.
Default: ',' (comma)
ColumnLength
Specifies the length of the virtual columns; to use this syntax element, you must know the length of each virtual column.
If ColumnLength specifies only one value and OutputColumns specifies multiple columns, the specified value applies to every output_column.
If ColumnLength specifies multiple values, it must specify a value for each output_column. The nth datatype corresponds to the nth output_column. However, the last output_column can be an asterisk (*), which represents a single virtual column that contains the remaining data. For example, if the first three virtual columns have the lengths 2, 1, and 3, and all remaining data belongs to the fourth virtual column, you can specify ColumnLength ('2', '1', '3', *).
Do not specify both this and the Delimiter syntax.
Regex
Specifies a regular expression that describes a row of packed data, enabling the function to find the data values.
A row of packed data contains a data value for each virtual column, but the row might also contain other information (such as the virtual column name). In the regular expression, each data value is enclosed in parentheses.
For example, a packed data has two virtual columns, age and gender, and that one row of packed data is age:34,gender:male. The regular expression that describes the row is '.*:(.*)'. The .*: matches the virtual column names, age and gender, and the (.*) matches the values, 34 and male.
To represent multiple data groups in regular expression, use multiple pairs of parentheses. Without parentheses, the last data group in regular expression represents the data value (other data groups are assumed to be virtual column names or unwanted data). If a different data group represents the data value, specify its group number with the RegexSet syntax element.
Default: '(.*)', matches the whole string (between delimiters, if any). When applied to the preceding sample row, the default regular expression causes the function to return 'age:34' and 'gender:male' as data values.
RegexSet
Specifies the ordinal number of the data group in regular expression that represents the data value in a virtual column.
Default behavior: The last data group in regular expression represents the data value. For example, a regular expression is '([a-zA-Z]*):(.*)'. If group number is '1', '([a-zA-Z]*)' represents the data value. If group number is '2', '(.*)' represents the data value.
Maximum: 30
IgnoreInvalid
Specifies whether the function ignores rows that contain invalid data.
IgnoreInvalid may not behave as you expect if an item in a virtual column has trailing special characters. See Example: Input Columns with Trailing Special Characters.
Default: 'false' (The function fails if it encounters a row with invalid data.)
Accumulate
Specifies the input columns to copy to the output table.