↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

Functions / String Functions And Operators

substring

Read the PG 18 manual

Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified.

Reading PG 18current·documented in 27 of 27 versions, 7.1 to 20

7.17.27.37.48.08.18.28.38.49.09.19.29.39.49.59.61011121314151617181920

Signatures & examples

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text
Extracts the substring of string starting at the start'th character if that is specified, and stopping after count characters if that is specified. Provide at least one of start and count.
substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th
substring ( string text FROM pattern text ) → text
Extracts the first substring matching POSIX regular expression; see Section 9.7.3.
substring('Thomas' from '...$') → mas
substring ( string text SIMILAR pattern text ESCAPE escape text ) → text
Extracts the first substring matching SQL regular expression; see Section 9.7.2. The first form has been specified since SQL:2003; the second form was only in SQL:1999 and should be considered obsolete.
substring('Thomas' similar '%#"o_a#"_' escape '#') → oma
substring ( string text FROM pattern text FOR escape text ) → text
Extracts the first substring matching SQL regular expression; see Section 9.7.2. The first form has been specified since SQL:2003; the second form was only in SQL:1999 and should be considered obsolete.
substring('Thomas' similar '%#"o_a#"_' escape '#') → oma
substring ( bytes bytea [ FROM start integer ] [ FOR count integer ] ) → bytea
Extracts the substring of bytes starting at the start'th byte if that is specified, and stopping after count bytes if that is specified. Provide at least one of start and count.
substring('\x1234567890'::bytea from 3 for 2) → \x5678
substring ( bits bit [ FROM start integer ] [ FOR count integer ] ) → bit
Extracts the substring of bits starting at the start'th bit if that is specified, and stopping after count bits if that is specified. Provide at least one of start and count.
substring(B'110010111111' from 3 for 2) → 00
substring(string similar pattern escape escape-character)
The substring function with three parameters provides extraction of a substring that matches an SQL regular expression pattern. The function can be written according to standard SQL syntax:
substring(string from pattern for escape-character)
The substring function with three parameters provides extraction of a substring that matches an SQL regular expression pattern. The function can be written according to standard SQL syntax:
substring(string, pattern, escape-character)
As with SIMILAR TO, the specified pattern must match the entire data string, or else the function fails and returns null. To indicate the part of the pattern for which the matching data sub-string is of interest, the pattern should contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these separators is returned when the match is successful.
Version history 7
  1. 18 → 19 changed
  2. 13 → 14 changed
  3. 12 → 13 changed
  4. 11 → 12 changed
  5. 8.0 → 8.1 changed
  6. 7.2 → 7.3 changed
  7. 7.1 → 7.2 changed

Definition snapshot: b7bd9cda · English manual source