PostgreSQL: Documentation: 17: 9.5. Binary String Functions and Operators

Documentation → PostgreSQL 17

Supported Versions: Current (18) / 17 / 16 / 15 / 14

Development Versions: 19 / devel

Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2

9.5. Binary String Functions and Operators
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.5. Binary String Functions and Operators #

This section describes functions and operators for examining and manipulating binary strings, that is values of type bytea. Many of these are equivalent, in purpose and syntax, to the text-string functions described in the previous section.

SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9.11. PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9.12).

Table 9.11. SQL Binary String Functions and Operators

Function/Operator Description Example(s)
`bytea` `\|\|` `bytea` → `bytea` Concatenates the two binary strings. `'\x123456'::bytea \|\| '\x789a00bcde'::bytea` → `\x123456789a00bcde`
`bit_length` ( `bytea` ) → `integer` Returns number of bits in the binary string (8 times the `octet_length`). `bit_length('\x123456'::bytea)` → `24`
`btrim` ( `bytes` `bytea`, `bytesremoved` `bytea` ) → `bytea` Removes the longest string containing only bytes appearing in `bytesremoved` from the start and end of `bytes`. `btrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x345678`
`ltrim` ( `bytes` `bytea`, `bytesremoved` `bytea` ) → `bytea` Removes the longest string containing only bytes appearing in `bytesremoved` from the start of `bytes`. `ltrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x34567890`
`octet_length` ( `bytea` ) → `integer` Returns number of bytes in the binary string. `octet_length('\x123456'::bytea)` → `3`
`overlay` ( `bytes` `bytea` `PLACING` `newsubstring` `bytea` `FROM` `start` `integer` [ `FOR` `count` `integer` ] ) → `bytea` Replaces the substring of `bytes` that starts at the `start`'th byte and extends for `count` bytes with `newsubstring`. If `count` is omitted, it defaults to the length of `newsubstring`. `overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3)` → `\x12020390`
`position` ( `substring` `bytea` `IN` `bytes` `bytea` ) → `integer` Returns first starting index of the specified `substring` within `bytes`, or zero if it's not present. `position('\x5678'::bytea in '\x1234567890'::bytea)` → `3`
`rtrim` ( `bytes` `bytea`, `bytesremoved` `bytea` ) → `bytea` Removes the longest string containing only bytes appearing in `bytesremoved` from the end of `bytes`. `rtrim('\x1234567890'::bytea, '\x9012'::bytea)` → `\x12345678`
`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`
`trim` ( [ `LEADING` \| `TRAILING` \| `BOTH` ] `bytesremoved` `bytea` `FROM` `bytes` `bytea` ) → `bytea` Removes the longest string containing only bytes appearing in `bytesremoved` from the start, end, or both ends (`BOTH` is the default) of `bytes`. `trim('\x9012'::bytea from '\x1234567890'::bytea)` → `\x345678`
`trim` ( [ `LEADING` \| `TRAILING` \| `BOTH` ] [ `FROM` ] `bytes` `bytea`,

Function/Operator

Description

Example(s)

bytea || bytea → bytea

Concatenates the two binary strings.

'\x123456'::bytea || '\x789a00bcde'::bytea → \x123456789a00bcde

bit_length ( bytea ) → integer

Returns number of bits in the binary string (8 times the octet_length).

bit_length('\x123456'::bytea) → 24

btrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start and end of bytes.

btrim('\x1234567890'::bytea, '\x9012'::bytea) → \x345678

ltrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start of bytes.

ltrim('\x1234567890'::bytea, '\x9012'::bytea) → \x34567890

octet_length ( bytea ) → integer

Returns number of bytes in the binary string.

octet_length('\x123456'::bytea) → 3

overlay ( bytes bytea PLACING newsubstring bytea FROM start integer [ FOR count integer ] ) → bytea

Replaces the substring of bytes that starts at the start'th byte and extends for count bytes with newsubstring. If count is omitted, it defaults to the length of newsubstring.

overlay('\x1234567890'::bytea placing '\002\003'::bytea from 2 for 3) → \x12020390

position ( substring bytea IN bytes bytea ) → integer

Returns first starting index of the specified substring within bytes, or zero if it's not present.

position('\x5678'::bytea in '\x1234567890'::bytea) → 3

rtrim ( bytes bytea, bytesremoved bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the end of bytes.

rtrim('\x1234567890'::bytea, '\x9012'::bytea) → \x12345678

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

trim ( [ LEADING | TRAILING | BOTH ] bytesremoved bytea FROM bytes bytea ) → bytea

Removes the longest string containing only bytes appearing in bytesremoved from the start, end, or both ends (BOTH is the default) of bytes.

trim('\x9012'::bytea from '\x1234567890'::bytea) → \x345678

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] bytes bytea,