Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

EXTCOLS

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 lines may be empty.

Syntax

EXTCOLS(text-expression [start [numcols]])

Parameters

text-expression

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.

start

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 is empty.

numcols

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 includes only existing characters. EXTCOLS does not return spaces at the end of the line to fill in the missing columns.

Examples

Example 7-84 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