入力
入力テーブルville_tempdata1は前の例の入力テーブルと似ていますが、パックされたデータの仮想列を分離する区切り記号がない点が異なります。関数が仮想列を決定できるようにするため、関数呼び出しは列の長さを指定します。
ville_tempdata1sn |
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呼び出し
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;
出力
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 |