EXTRACT

Syntax

EXTRACT {dst_txt_var|date_var} FROM {{src_txt_lit|_var|_col}|{src_date_var|_col}} {start_num_lit|_var}{length_num_lit|_var}

Description

Copies a portion of a string into a string variable.

You must specify the starting location of the string as an offset from the beginning of the string and its length. An offset of 0 (zero) begins at the leftmost character; an offset of 1 begins one character beyond that, and so on.

If the source is a date variable or column, it is converted to a string before the extraction according to the following rules:

  • For DATETIME columns and SQR DATE variables, SQR specifies the SQR_DB_DATE_FORMAT setting.

    If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

  • For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

    If this has not been set, SQR uses the format listed in the DATE Column Formats table.

  • For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

    If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

If the destination is a date variable, the string extracted from the source must be in one of the following formats:

  • The format specified by the SQR_DB_DATE_FORMAT setting.

  • One of the database-dependent formats (see the Default Database Formats table).

  • The database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

Parameters

Parameter Description

dst_txt_var | date_var

Specifies a text or date variable into which the extracted string is placed.

{src_txt_lit|_var|_col}|

{src_date_var|_col}

Specifies a text or date variable, column, or literal from which the string is to be extracted.

start_num_lit|_var

Specifies starting location of the string to be extracted.

length_num_lit|_var

Specifies length of the string to be extracted.

Example

The following example illustrates the EXTRACT command: 

extract  $state  from  $record  45  2
extract  $foo from "SQR Rocks" 0 4 ! $foo='SQR'

code  from  &phone  0  3
extract  $zip_four  from  &zip  5  4
extract  $rec  from  $tape_block  #loc  #rec_len

Note:

Oracle recommends that you not use the EXTRACT command when processing strings.

See The substr function described in the Miscellaneous Functions table under the LET command

See FIND

See The PRINT command for information about default date and time formats