SUBSTRING()

Available in: DSQL, ESQL, PSQL

Added in: 1.0

Description: Returns a string's substring starting at the given position, either to the end of the string or with a given length.

Return type: CHAR(n)

Syntax:

SUBSTRING(<str> FROM startpos [FOR length])

<str> := any expression evaluating to a string
startpos and length must be integer literals

SUBSTRING returns the stream of bytes starting at byte position startpos (the first byte position being 1). Without the FOR argument, it returns all the remaining bytes in the string. With FOR, it returns length bytes or the remainder of the string, whichever is shorter.

SUBSTRING can be used with:

Any string or (var)char argument, regardless of its character set;
Subtype 0 (binary) BLOBs;
Subtype 1 (text) BLOBs, if the character set has 1 byte per character.

SUBSTRING can not be used with text BLOBs that have an underlying multi-byte character set.

Example:

insert into AbbrNames(AbbrName)
  select substring(LongName from 1 for 3) from LongNames