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 |
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 |