SCAN Function

Returns the nth word from a character expression.

Category: Character
Returned data type: NCHAR

Syntax

SCAN(expression, n [, delimiters])

Arguments

expression

specifies any valid expression that evaluates to a character string.

Data type DOUBLE
See <sql-expression>
FedSQL Expressions

n

is a nonzero numeric expression that specifies the number of the word in the character expression that you want SCAN to select. The following rules apply:

  • If n is positive, SCAN counts words from left to right in the character string.
  • If n is negative, SCAN counts words from right to left in the character string.
  • If n is greater than the number of words in expression, SCAN returns a blank value.

delimiters

specifies any valid expression that evaluates to a character string and that SCAN uses as word separators in the expression.

Default
Requirement If delimiter is a constant, enclose delimiter in single quotation marks.
Interactions ASCII default delimiters are: blank ! $ % & ( ) * + , – . / ; < |. In environments without the ^ character, SCAN uses the ~ character instead.
EBCDIC default delimiters are: blank ! $ % & ( ) * + , – . / ; < ¬ | ¢.
Data type NCHAR
See <sql-expression>
FedSQL Expressions

Details

Leading delimiters before the first word in the expression do not effect SCAN. If there are two or more contiguous delimiters, SCAN treats them as one.

Example

The following statements illustrate the SCAN function:
Statements
Results
select scan('ABC.DEF(X=Y)', 3);
X=Y
select scan('ABC.DEF(X=Y)', -3);
ABC
Last updated: February 23, 2017