FIXED_COLUMN_LOG_PARSE

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

Syntax

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

Examples

The easiest way to understand FIXED_COLUMN_LOG_PARSE is to review the examples below.

Notes

  • The first character of the string value expression is at position 0.
  • If the output data type isn’t specified, it defaults to CHAR and has a data length the size of the input expression.
  • If the output data type is specified as TYPE CHAR or TYPE VARCHAR without a length, it is assigned a length of just 1 (excess characters will be truncated).
  • If the length (FOR) part of the data type isn’t specified, the field will run to the end of the input.
  • TIME, DATE and TIMESTAMP column types support a Java-style datetimeformat; see DATE, TIMESTAMP, TIME below.
  • Binary coded input can be extracted using the optional BINARY-DATA-COLUMN specifier - see the binary data examples below.

Other similar parsing functions include:

Simple example

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

Multiline String Literal as column description

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

DATE, TIMESTAMP, TIME

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'

Not Null Types, rejecting missing data

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

Binary Data, Fixed Point

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

Binary Data, Floating Point

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

Representing NaN (Not-A-Number)

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