TEXTTABLE

TEXTTABLE関数は、文字入力を処理して表形式の出力を生成します。固定ファイル形式と区切りファイル形式の両方の解析をサポートしています。関数自身が、どの列を投影するかを定義します。TEXTTABLE関数は暗黙的にネストされたテーブルであり、先行するFROM Clause 項目に相関している可能性があります。

Usage

TEXTTABLE(expression [SELECTOR string] COLUMNS <COLUMN>, ... [NO ROW DELIMITER | ROW DELIMITER char] [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer] [NO TRIM]) AS name

WHERE <COLUMN>の場合

COLUMN := name (FOR ORDINALITY | ([HEADER string] datatype [WIDTH integer [NO TRIM]] [SELECTOR string integer]))

この関数には以下のパラメータがあります：

To view the full table, click the expand button in its top right corner

Parameter

Description

expression

Text content to process, should be convertible to CLOB

SELECTOR

Specifies that delimited lines should only match if the line begins with the selector string followed by a delimiter. The selector value is a valid column value. If a TEXTTABLE SELECTOR is specified, a SELECTOR may also be specified for column values. A column SELECTOR argument will select the nearest preceding text line with the given SELECTOR prefix and select the value at the given 1-based integer position (which includes the selector itself). If no such text line or position with a given line exists, a null value will be produced

NO ROW DELIMITER

Indicates that fixed parsing should not assume the presence of newline row delimiters

ROW DELIMITER

Sets the row delimiter/new line to an alternate character. Default: new line character with built-in handling for treating the carriage-return new line as a single character. If ROW DELIMITER is specified, carriage return will be given no special treatment

DELIMITER

Sets the field delimiter character to use. Default: ,

QUOTE

Sets the quote (or qualifier) character used to wrap field values. Default: ". May be set to nothing by specifying QUOTE ''

ESCAPE

Sets the escape character to use if no quoting character is in use. This is used in situations where the delimiter or newline characters are escaped with a preceding character, e.g. \

HEADER

Set for the TEXTTABLE specifies the text line number (counting every new line) on which the column names occur. All lines before the header will be skipped. If HEADER is specified, the header line will be used to determine the TEXTTABLE column position by case-insensitive name matching. This is especially useful when only a subset of the columns is needed. If the HEADER value is not specified, it defaults to 1. If HEADER is not specified, then columns are expected to match positionally with the text contents. If the HEADER option for a column is specified, then that will be used as the expected header name.

SKIP

Specifies the number of text lines (counting every new line) to skip before parsing the contents. HEADER may still be specified with SKIP

A FOR ORDINALITY

This column is typed as an integer and will return the 1-based item number as its value

WIDTH

Indicates the fixed-width length of a column in characters, not bytes. With the default ROW DELIMITER, a CR NL sequence counts as a single character

NO TRIM

When specified on the TEXTTABLE, will affect all column and header values. When specified on a column, the fixed or unqualified text value not be trimmed of a leading and trailing whitespace

SKIPERRORS[maxerrors]

ERRORLOG tablename

Failed lines are written into the named table using the following format:

tstamp (timestamp)	RequestID	SessionID	linenumber (integer)	data (string)	errorMessage (string)
timestamp of the failure	RequestID	SessionID	row nr starting from 1	the row data as string	the error message

Syntax Rules

widthが1つの列に指定された場合、それはすべての列に指定されなければならず、非負の整数でなければなりません；
widthが指定されている場合、固定幅の構文解析が使用され、ESCAPE、QUOTE、SELECTOR、HEADERは指定されるべきではありません；
width が指定されていない場合、NO ROW DELIMITER；
カラム名には重複があってはなりません；
QUOTE , DELIMITER , ROW DELIMITERはすべて異なる文字でなければなりません。

Examples

1. HEADERパラメータを使用すると、1 行 ['b'] が返されます：

SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x

2. 固定幅を使用し、2行 ['a', 'b', 'c'], ['d', 'e', 'f'] を返します：

SELECT * FROM TEXTTABLE(UNESCAPE('abc\ndef') COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x

3. 行区切り文字なしで固定幅を使用すると、3行 ['a'], ['b'], ['c'] が返されます：

SELECT * FROM TEXTTABLE('abc' COLUMNS col1 string width 1 NO ROW DELIMITER) x

4. ESCAPEパラメータを使用すると、1 行 ['a,', 'b'] を返します：

SELECT * FROM TEXTTABLE('a:,,b' COLUMNS col1 string, col2 string ESCAPE ':') x

5. ネストされたテーブルとして：

SELECT x.* FROM t, TEXTTABLE(t.clobcolumn COLUMNS first string, second date SKIP 1) x

6. SELECTOR s を使用すると、2 行 ['c', 'd', 'b'], ['c', 'f', 'b'] が返されます：

SELECT * FROM TEXTTABLE(UNESCAPE('a,b\nc,d\nc,f') SELECTOR 'c' COLUMNS col1 string, col2 string, col3 string SELECTOR 'a' 2) x

7. ローカル・ファイル・システムから CSV ファイルを読み込む際に、エスケープされたリテラルを使用して、特定の文字を列と行の区切り文字として設定します（ここでは、キャリッジ・リターンを列の区切り文字として、ライン・フィードを行の区切り文字として設定します）：

SELECT
"csv_table".* 
FROM (CALL "ds_file".getFiles('planets_export.csv')) f,
    TEXTTABLE(to_chars(f.file,'utf-8') 
        COLUMNS 
 "id" STRING
        , "name" STRING
        , "population" STRING
        , "diameter" STRING
        , "gravity" STRING
        ROW DELIMITER E'\n'
        DELIMITER E'\r'
        QUOTE '"' 
        MAXWIDTH 8000
        SKIP 1
    )
"csv_table";;

8. 任意の Unicode 文字を、その番号を参照しながら区切り文字として指定します。上記と同じCASEですが、実装が異なります：

SELECT
"csv_table".* 
FROM (CALL "ds_file".getFiles('planets_export.csv')) f,
    TEXTTABLE(to_chars(f.file,'utf-8') 
        COLUMNS 
 "id" STRING
        , "name" STRING
        , "population" STRING
        , "diameter" STRING
        , "gravity" STRING
        ROW DELIMITER E'\u000A'
        DELIMITER E'\u000D'
        QUOTE '"' 
        MAXWIDTH 8000
        SKIP 1
    )
"csv_table";;

Usage

Syntax Rules

Examples

See Also