SUBSTRING()

Procedural SQL (PSQL) StatementsAggregate Functions

`SUBSTRING()`

Extracts a substring by position and length, or by SQL regular expression

Result types

VARCHAR or BLOB

Syntax

SUBSTRING ( <substring-args> )

<substring-args> ::=
    str FROM startpos [FOR length]
  | str SIMILAR <similar-pattern> ESCAPE <escape>

<similar-pattern> ::=
  <similar-pattern-R1>
  <escape> " <similar-pattern-R2> <escape> "
  <similar-pattern-R3>

Table 1. `SUBSTRING` Function Parameters
Parameter	Description
str	An expression of a string type
startpos	Integer expression, the position from which to start retrieving the substring
length	The number of characters to retrieve after the startpos
similar-pattern	SQL regular expression pattern to search for the substring
escape	Escape character

Returns a string’s substring starting at the given position, either to the end of the string or with a given length, or extracts a substring using an SQL regular expression pattern.

If any argument is NULL, the result is also NULL.

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.

Positional `SUBSTRING`

In its simple, positional form (with FROM), this function returns the substring starting at character position startpos (the first character 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.

When startpos is smaller than 1, substring behaves as if the string has 1 - startpos extra positions before the actual first character at position 1.The length is considered from this imaginary start of the string, so the resulting string could be shorter than the specified length, or even empty.

The function fully supports binary and text BLOBs of any length, and with any character set.If str is a BLOB, the result is also a BLOB.For any other argument type, the result is a VARCHAR.

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'.

Example

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

select substring('abcdef' from 1 for 2) from rdb$database;
-- result: 'ab'

select substring('abcdef' from 2) from rdb$database;
-- result: 'bcdef'

select substring('abcdef' from 0 for 2) from rdb$database;
-- result: 'a'
-- and NOT 'ab', because there is "nothing" at position 0

select substring('abcdef' from -5 for 2) from rdb$database;
-- result: ''
-- length ends before the actual start of the string

SUBSTRING()

SUBSTRING()

Positional SUBSTRING

`SUBSTRING()`

Positional `SUBSTRING`