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.
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_MLE ( ON ville_tempdata1 USING TargetColumn ('packed_temp_data') OutputColumns ('city','state','temp_F') OutputDataTypes ('varchar(9)','varchar(9)','float') 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
Download a zip file of all examples and a SQL script file that creates their input tables.