Binary String Functions and Operators
| PostgreSQL 9.5.3 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 9. Functions and Operators | Next |
This section describes functions and operators for examining and manipulating values of type bytea .
SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9-9 . PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-10 ).
Note: The sample results shown on this page assume that the server parameter bytea_output is set to escape (the traditional PostgreSQL format).
Table 9-9. SQL Binary String Functions and Operators
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| string || string | bytea | String concatenation | E'\\\\Post'::bytea || E'\\047gres\\000'::bytea | \\Post'gres\000 |
octet_length(
string
)
|
int | Number of bytes in binary string | octet_length(E'jo\\000se'::bytea) | 5 |
overlay(
string
placing
string
from
int
[
for
int
])
|
bytea | Replace substring | overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) | T\\002\\003mas |
position(
substring
in
string
)
|
int | Location of specified substring | position(E'\\000om'::bytea in E'Th\\000omas'::bytea) | 3 |
substring(
string
[
from
int
] [
for
int
])
|
bytea | Extract substring | substring(E'Th\\000omas'::bytea from 2 for 3) | h\000o |
trim([
both
]
bytes
from
string
)
|
bytea | Remove the longest string containing only the bytes in bytes from the start and end of string | trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) | Tom |
Additional binary string manipulation functions are available and are listed in Table 9-10 . Some of them are used internally to implement the SQL -standard string functions listed in Table 9-9 .
Table 9-10. Other Binary String Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
btrim(
string
bytea
,
bytes
bytea
)
|
bytea | Remove the longest string consisting only of bytes in bytes from the start and end of string | btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) | trim |
decode(
string
text
,
format
text
)
|
bytea |
Decode binary data from textual representation in
string
.
Options for
format
are same as in
encode
.
|
decode(E'123\\000456', 'escape') | 123\000456 |
encode(
data
bytea
,
format
text
)
|
text | Encode binary data into a textual representation. Supported formats are: base64 , hex , escape . escape converts zero bytes and high-bit-set bytes to octal sequences ( \ nnn ) and doubles backslashes. | encode(E'123\\000456'::bytea, 'escape') | 123\000456 |
get_bit(
string
,
offset
)
|
int | Extract bit from string | get_bit(E'Th\\000omas'::bytea, 45) | 1 |
get_byte(
string
,
offset
)
|
int | Extract byte from string | get_byte(E'Th\\000omas'::bytea, 4) | 109 |
length(
string
)
|
int | Length of binary string | length(E'jo\\000se'::bytea) | 5 |
md5(
string
)
|
text | Calculates the MD5 hash of string , returning the result in hexadecimal | md5(E'Th\\000omas'::bytea) | 8ab2d3c9689aaf18 b4958c334c82d8b1 |
set_bit(
string
,
offset
,
newvalue
)
|
bytea | Set bit in string | set_bit(E'Th\\000omas'::bytea, 45, 0) | Th\000omAs |
set_byte(
string
,
offset
,
newvalue
)
|
bytea | Set byte in string | set_byte(E'Th\\000omas'::bytea, 4, 64) | Th\000o@as |
get_byte
and
set_byte
number the first byte
of a binary string as byte 0.
get_bit
and
set_bit
number bits from the
right within each byte; for example bit 0 is the least significant bit of
the first byte, and bit 15 is the most significant bit of the second byte.
See also the aggregate function
string_agg
in
Section 9.20
and the large object functions
in
Section 32.4
.