| Firebird Documentation Index → Firebird 2.0 Language Ref. Update → Internal functions → SUBSTRING() |
|
|
|
Find a more recent version at Firebird 5.0 Language Reference: 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() |