Firebird Documentation IndexFirebird 2.0 Language Ref. UpdateInternal functions → SUBSTRING()
Firebird Home Firebird Home Prev: RDB$SET_CONTEXT()Firebird Documentation IndexUp: Internal functionsNext: TRIM()

SUBSTRING()

Available in: DSQL, PSQL

Added in: 1.0

Changed in: 2.0

Description: Returns a 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 pos [FOR len])

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:

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.

Prev: RDB$SET_CONTEXT()Firebird Documentation IndexUp: Internal functionsNext: TRIM()
Firebird Documentation IndexFirebird 2.0 Language Ref. UpdateInternal functions → SUBSTRING()