FIXED_COLUMN_LOG_PARSE can extract multiple output values from an input character expression (the first parameter) by considering the string value expression as one or more fixed-width (possibly overlapping) fields that defined by the column description.
These input fields are then converted automatically (CAST) to the output SQL types specified in the column description (the second parameter).
FIXED_COLUMN_LOG_PARSE ( <string value expression>, <column description string expression> )
<column description string expression> := '[ BINARY-DATA-COLUMN: ] <column description> [,...]'
<column description> :=
<identifier> [ TYPE <data type> [ <datetimeformat> ]] [ NOT NULL ]
START <numeric value expression> [FOR <numeric constant expression>]
The easiest way to understand FIXED_COLUMN_LOG_PARSE is to review the examples below.
Other similar parsing functions include:
This shows a single character expression being parsed out into two output columns INTEGER and BOOLEAN:
select t.r.a, t.r.b from
(values (FIXED_COLUMN_LOG_PARSE(' 1 true', 'a TYPE Integer START 0 FOR 3, b TYPE boolean START 4')))
t(r);
+----+-------+
| A | B |
+----+-------+
| 1 | true |
+----+-------+
Here the SQL allows the rather lengthy column description to be written as a multiline expresson (lines 3-5 below). The newlines are treated as white space in the deacription.
select t.r.a, t.r.b, t.r.c from
(values (FIXED_COLUMN_LOG_PARSE(' 1 true 423.345 ',
'a TYPE Integer START 0 FOR 3, '
'b TYPE boolean START 4 FOR 6, '
'c TYPE numeric(5,2) START 10')))
t(r);
+----+-------+---------+
| A | B | C |
+----+-------+---------+
| 1 | true | 423.35 |
+----+-------+---------+
Any character string that can be CAST to a DATE, TIMESTAMP or TIME can also be extracted using FIXED_COLUMN_LOG_PARSE.
Note how the column a date format has to be surrounded by pairs of single quotes in the SQL example; the actual expression only contains one single quote before and one single quote after the date format.
select t.r.a, t.r.b, t.r.c from
(values (FIXED_COLUMN_LOG_PARSE('2021-01-02 12:34:56.789',
'a TYPE DATE ''yyyy-MM-dd'' NOT NULL START 0 FOR 10, '
'b TYPE TIMESTAMP START 0 FOR 23, '
'c TYPE TIME START 11 FOR 8')))
t(r);
+------------+-------------------------+----------+
| A | B | C |
+------------+-------------------------+----------+
| 2021-01-02 | 2021-01-02 12:34:56.789 | 12:34:56 |
+------------+-------------------------+----------+
Column specifications for types DATE,TIME and TIMESTAMP also support a format parameter allowing the user to specify exact time component layout. The parser uses the Java SimpleDateFormat class to parse the strings for types DATE, TIME and TIMESTAMP. The Date and Time Patterns topic gives a full description and examples of timestamp format strings. The following is an example of a column definition with a format string:
"name" TYPE TIMESTAMP 'dd/MMM/yyyy:HH:mm:ss'
If there is no numeric data at the right position in the data expression, then a NULL value would be returned; if the output column has been defined as NOT NULL then the whole row will be rejected.
select t.r.a, t.r.b, t.r.c from
(values (FIXED_COLUMN_LOG_PARSE(' 1 ',
'a TYPE Integer NOT NULL START 0 FOR 3, '
'b TYPE boolean NOT NULL START 4 FOR 6, '
'c TYPE numeric(5,2) NOT NULL START 10')))
t(r);
+----+----+----+
| A | B | C |
+----+----+----+
+----+----+----+
As well as parsing text into numeric columns, FIXED_COLUMN_LOG_PARSE can parse binary-coded data; here into fixed point integer types (SMALLINT up to BIGINT). Note how the column description expression starts with ‘BINARY-DATA-COLUMN:’. It is the whole string value expression which is treated as binary - either all column descriptions are binary, or none of them are.
select t.r.a1, t.r.a2, t.r.b1, t.r.b2, t.r.c2, t.r.c4, t.r.d4, t.r.d8 from
(values (FIXED_COLUMN_LOG_PARSE(u&'\00DE\00AD\00BE\00EF',
'BINARY-DATA-COLUMN: '
'a1 TYPE TINYINT START 0 FOR 1, a2 TYPE SMALLINT START 0 FOR 1, '
'b1 TYPE TINYINT START 1 FOR 1, b2 TYPE SMALLINT START 1 FOR 1, '
'c2 TYPE SMALLINT START 2 FOR 2, c4 TYPE INTEGER START 2 FOR 2, '
'd4 TYPE INTEGER START 0 FOR 4, d8 TYPE BIGINT START 0 FOR 4'))) t(r);
+------+------+------+------+---------+--------+-------------+-------------+
| A1 | A2 | B1 | B2 | C2 | C4 | D4 | D8 |
+------+------+------+------+---------+--------+-------------+-------------+
| -34 | 222 | -83 | 173 | -16657 | 48879 | -559038737 | 3735928559 |
+------+------+------+------+---------+--------+-------------+-------------+
FIXED_COLUMN_LOG_PARSE can also parse binary-coded data into floating point (REAL and DOUBLE) output columns. This example shows pi (3.14159265358979323846), expressed as 4-byte single-precision real and as 8-byte double-precision float:
select t.r.r4, t.r.d4, t.r.r8, t.r.d8 from
(values (FIXED_COLUMN_LOG_PARSE(u&'\0040\0049\000F\00DB\0040\0009\0021\00FB\0054\0044\002D\0018',
'BINARY-DATA-COLUMN: '
'r4 TYPE REAL START 0 FOR 4, d4 TYPE FLOAT START 0 FOR 4, '
'r8 TYPE REAL START 4 FOR 8, d8 TYPE FLOAT START 4 FOR 8'))) t(r);
+------------+---------------------+------------+--------------------+
| R4 | D4 | R8 | D8 |
+------------+---------------------+------------+--------------------+
| 3.1415927 | 3.1415927410125732 | 3.1415927 | 3.141592653589793 |
+------------+---------------------+------------+--------------------+
The binary representation of NaN can be expressed as REAL or FLOAT:
select t.r.r4, t.r.d4, t.r.r8, t.r.d8 from
(values (FIXED_COLUMN_LOG_PARSE(u&'\007F\00C0\0000\0000\007F\00F8\0000\0000\0000\0000\0000\0000',
'BINARY-DATA-COLUMN: '
'r4 TYPE REAL START 0 FOR 4, d4 TYPE FLOAT START 0 FOR 4, '
'r8 TYPE REAL START 4 FOR 8, d8 TYPE FLOAT START 4 FOR 8'))) t(r);
+------+------+------+------+
| R4 | D4 | R8 | D8 |
+------+------+------+------+
| NaN | NaN | NaN | NaN |
+------+------+------+------+