| Firebird Documentation Index → Firebird 2.0 Language Ref. Update → Internal functions → 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 (strFROMpos[FORlen])str::= a string expressionpos::= an integer expressionlen::= 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
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.
|
| Firebird Documentation Index → Firebird 2.0 Language Ref. Update → Internal functions → SUBSTRING() |