VARIABLE_COLUMN_LOG_PARSE splits an input string (its first argument,
VARIABLE_COLUMN_LOG_PARSE( <character-expression>, <columns>, <delimiter-string> [ , <escape-string>, <quote-string> ] ) <columns> := <number of columns> | <list of columns> <number of columns> := <numeric value expression> <list of columns> := '<column description>[, ...]' <column description> := <identifier> TYPE <data type> [ NOT NULL ] <delimiter string> := <character-expression> <escape-string> := <character-expression> <quote-string> := '<begin quote character> [ <end quote character> ]')
Note: Parsing of binary files is not supported.
The arguments <escape-string> and <quote-string> are optional. Specifying an <escape-string> allows the value of a field to contain an embedded delimiter. As a simple example, if the
Since SQLstream supports unicode character literals, a tab can also be a delimiter, specified using a unicode escape, such as u&’\0009’, which is a string consisting only of a tab character.
Specifying a <quote-string> is another way to hide an embedded delimiter. The <quote-string> should be a one or two character expression: the first is used as the <begin quote character> character; the second, if present, is used as the
Note that the <begin quote character> and <end quote character> are single characters and can be different. The <begin quote character> can be used to start and end the quoted string, or the
When a list of columns <list of columns> is supplied as the second parameter <columns>, the column specifications (<column description>) for types DATE, TIME, and TIMESTAMP support a format parameter allowing the user to specify exact time component layout. The parser uses the Java class java.lang.SimpleDateFormat to parse the strings for those types. The Date and Time Patterns topic gives a full description of timestamp format strings, with examples.
The following is an example of a column definition with a format string:
"name" TYPE TIMESTAMP 'dd/MMM/yyyy:HH:mm:ss'
By default, the output columns are named COLUMN1, COLUMN2, COLUMN3, etc., each of SQL data type VARCHAR(1024)
See also REGEX_LOG_PARSE.