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.
ville_tempdata1
sn |
packed_temp_data |
10 |
NashvilleTennessee35.1 |
11 |
NashvilleTennessee36.2 |
12 |
NashvilleTennessee34.5 |
13 |
NashvilleTennessee33.6 |
14 |
NashvilleTennessee33.1 |
15 |
NashvilleTennessee33.2 |
16 |
NashvilleTennessee32.8 |
17 |
NashvilleTennessee32.4 |
18 |
NashvilleTennessee32.2 |
19 |
NashvilleTennessee32.4 |
20 |
Thisisbaddata |
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
city |
state |
temp_f |
sn |
Nashville |
Tennessee |
35.1 |
10 |
Nashville |
Tennessee |
36.2 |
11 |
Nashville |
Tennessee |
34.5 |
12 |
Nashville |
Tennessee |
33.6 |
13 |
Nashville |
Tennessee |
33.1 |
14 |
Knoxville |
Tennessee |
33.2 |
15 |
Knoxville |
Tennessee |
32.8 |
16 |
Knoxville |
Tennessee |
32.4 |
17 |
Knoxville |
Tennessee |
32.2 |
18 |
Knoxville |
Tennessee |
32.4 |
19 |