meta data for this page
SUBSTRING()
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: