Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-02 |
|
|
View PDF |
The EXTCOLS function extracts specified columns from each line of a multiline text value. The function returns a multiline text value that includes only the extracted columns. Columns refer to the character positions in each line of a multiline text value. The first character in each line is in column one, the second is in column two, and so on.
Return Value
TEXT
or NTEXT
EXTCOLS always returns a text value that has the same number of lines as text-expression, though some of the lines may be empty.
Syntax
EXTCOLS(text-expression [start [numcols]])
Arguments
The TEXT
or NTEXT
expression from which the specified columns should be extracted. When text-expression is a multiline text value, the characters in the specified columns are extracted from each one of its lines.
An INTEGER
, between 1
and 4000
, that represents the column position at which to begin extracting. The column position of the first character in each line of text-expression is 1
. When you specify a starting column that is to the right of the last character in a given line in text expression, the corresponding line in the return value will be empty.
An INTEGER
that represents the number of columns to be extracted. When you do not specify numcols, EXTCOLS extracts all the characters from the starting column to the end of each line. When you specify a length that exceeds the number of characters that follow the starting position in a given line in text expression, the corresponding line in the return value will include only existing characters. EXTCOLS does not return spaces at the end of the line to fill in the missing columns.
Examples
Example 14-7 Extracting Text Columns
In this example, four columns are extracted from each line of citylist
, starting from the second column.
DEFINE citylist VARIABLE TEXT citylist = 'Boston\nHouston\nChicago'
The statement
SHOW citylist
produces the following output.
Boston Houston Chicago
The statement
SHOW EXTCOLS(citylist 2 4)
produces the following output.
osto oust hica