<< | FB 2.0 Language Reference | >>

SUBSTRING()

Available in: DSQL, PSQL

Added in: 1.0

Changed in: 2.0

Description

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

Result type: (VAR)CHAR(n)

Syntax

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

 str ::= a string expression
 pos ::= an integer expression
 len ::= an integer expression

This function returns the substring starting at character position pos (the first position being 1). Without the optional FOR argument, it returns all the remaining characters in the string. With it, it returns len characters or the remainder of the string, whichever is shorter.

Since Firebird 2.0, SUBSTRING fully supports multi-byte character sets.

In Firebird 1.x, pos and len had to be be integer literals. In 2.0 and above they can be any valid integer expression.

The result type is VARCHAR for a VARCHAR or BLOB argument, and CHAR for a CHAR or literal argument.

The width – in characters – of the result field is always equal to the length of str, regardless of pos and len.

So, substring('pinhead' from 4 for 2) will return a CHAR(7) containing the string 'he'.

SUBSTRING can be used with:

• Any string, (var)char or text BLOB argument, regardless of its character set;
• Subtype 0 (binary) BLOBs.

Example

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

Effect of NULLs:

• If str is NULL, the function returns NULL.
• If str is a valid string but pos and/or len is NULL, the function returns NULL but describes the result field as non-nullable. As a result, most clients (including isql) will incorrectly show the result as an empty string.

See also:
Firebird 2.0.4 Release Notes: Built-in function SUBSTRING() enhanced
Firebird Null Guide: Internal functions and directives

back to top of page
<< | FB 2.0 Language Reference | >>