FirebirdSQL logo

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

Regular Expression SUBSTRING

In the regular expression form (with SIMILAR), the SUBSTRING function returns part of the string matching an SQL regular expression pattern.If no match is found, NULL is returned.

The SIMILAR pattern is formed from three SQL regular expression patterns, R1, R2 and R3.The entire pattern takes the form of R1 || '<escape>"' || R2 || '<escape>"' || R3, where <escape> is the escape character defined in the ESCAPE clause.R2 is the pattern that matches the substring to extract, and is enclosed between escaped double quotes (<escape>", e.g. “#"” with escape character ‘#’).R1 matches the prefix of the string, and R3 the suffix of the string.Both R1 and R3 are optional (they can be empty), but the pattern must match the entire string.In other words, it is not sufficient to specify a pattern that only finds the substring to extract.

Tip

The escaped double quotes around R2 can be compared to defining a single capture group in more common regular expression syntax like PCRE.That is, the full pattern is equivalent to R1(R2)R3, which must match the entire input string, and the capture group is the substring to be returned.

Note

If any one of R1, R2, or R3 is not a zero-length string and does not have the format of an SQL regular expression, then an exception is raised.

The full SQL regular expression format is described in Syntax: SQL Regular Expressions

Examples
substring('abcabc' similar 'a#"bcab#"c' escape '#')  -- bcab
substring('abcabc' similar 'a#"%#"c' escape '#')     -- bcab
substring('abcabc' similar '_#"%#"_' escape '#')     -- bcab
substring('abcabc' similar '#"(abc)*#"' escape '#')  -- abcabc
substring('abcabc' similar '#"abc#"' escape '#')     -- <null>

TRIM()

Trims leading and/or trailing spaces or other substrings from a string

Result type

VARCHAR or BLOB

Syntax
TRIM ([<adjust>] str)

<adjust> ::=  {[<where>] [what]} FROM

<where> ::=  BOTH | LEADING | TRAILING
Table 1. TRIM Function Parameters
Parameter Description

str

An expression of a string type

where

The position the substring is to be removed from — BOTH | LEADING | TRAILING.BOTH is the default

what

The substring that should be removed (multiple times if there are several matches) from the beginning, the end, or both sides of the input string str.By default, it is space (' ')

Removes leading and/or trailing spaces (or optionally other strings) from the input string.

Note

If str is a BLOB, the result is a BLOB.Otherwise, it is a VARCHAR(n) with n the formal length of str.

Warning

When used on a BLOB, this function may need to load the entire object into memory.This may affect performance if huge BLOBs are involved.

TRIM Examples

select trim ('  Waste no space   ') from rdb$database
-- returns 'Waste no space'

select trim (leading from '  Waste no space   ') from rdb$database
-- returns 'Waste no space   '

select trim (leading '.' from '  Waste no space   ') from rdb$database
-- returns '  Waste no space   '

select trim (trailing '!' from 'Help!!!!') from rdb$database
-- returns 'Help'

select trim ('la' from 'lalala I love you Ella') from rdb$database
-- returns ' I love you El'

select trim ('la' from 'Lalala I love you Ella') from rdb$database
-- returns 'Lalala I love you El'