8.3. String Functions - 8.3.16. SUBSTRING() - 《Firebird 2.5 Language Reference》

8.3.16. SUBSTRING()

8.3.16. `SUBSTRING()`

Available in

DSQL, PSQL

Changed in

2.5.1

Syntax

SUBSTRING (str FROM startpos [FOR length])

Table 138. `SUBSTRING` Function Parameters
Parameter	Description
str	An expression of a string type
startpos	Integer expression, the position from which to start retrieving the substring
length	The number of characters to retrieve after the startpos

Result types

VARCHAR or BLOB

Description

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

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

In Firebird 1.x, startpos and length must be integer literals. In 2.0 and above they can be any valid integer expression.

Starting with Firebird 2.1, this function fully supports binary and text BLOBs of any length and character set. If str is a BLOB, the result is also a BLOB. For any other argument type, the result is a VARCHAR. Previously, the result type used to be CHAR if the argument was a CHAR or a string literal.

For non-BLOB arguments, the width of the result field is always equal to the length of str, regardless of startpos and length. So, substring('pinhead' from 4 for 2) will return a VARCHAR(7) containing the string 'he'.

If any argument is NULL, the result is NULL.

Bugs

If str is a BLOB and the length argument is not present, the output is limited to 32767 characters. Workaround: with long BLOBs, always specify char_length(str) — or a sufficiently high integer — as the third argument, unless you are sure that the requested substring fits within 32767 characters.
This bug has been fixed in version 2.5.1; the fix was also backported to 2.1.5.
An older bug in Firebird 2.0, which caused the function to return “false emptystrings” if startpos or length was NULL, was fixed.

Example

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

When used on a BLOB, this function may need to load the entire object into memory. Although it does try to limit memory consumption, this may affect performance if huge BLOBs are involved.