Regular Expression

Procedural SQL (PSQL) StatementsAggregate Functions

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.

Regular Expression

Regular Expression SUBSTRING

TRIM()

Regular Expression `SUBSTRING`

`TRIM()`