Added in: 1.0
Changed in: 2.0, 2.1
Returns a string's substring starting at the given position, either to the end of the string or with a given length.
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,
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
substring('pinhead' from 4 for 2) will return a
VARCHAR(7) containing the string
If any argument is
NULL, the result is
stris a BLOB and the
lengthargument 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.
- A bug in Firebird 2.0 which caused the function to return "false emptystrings" if
NULL, has been fixed.
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.