SUBSTRING()

<< SQRT() | FB 2.5 Language Reference | TAN() >>

SUBSTRING()

Available in: DSQL, PSQL

Added in: 1.0

Changed in: 2.0, 2.1, 2.1.5, 2.5.1

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: VARCHAR(n) or BLOB

Syntax 

 SUBSTRING (<str> FROM startpos [FOR 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(n). Previously, the result type used to be CHAR(n) if the argument was a CHAR(n) 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.
  • A bug in Firebird 2.0 which caused the function to return "false emptystrings" if startpos or length was NULL, has been fixed.

Example 

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

Warning: 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.

See also:
Firebird 2.0.4 Release Notes: Built-in function SUBSTRING() enhanced

back to top of page
<< SQRT() | FB 2.5 Language Reference | TAN() >>