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-11. PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-12).
Note: The sample results shown on this page assume that the server parameter
bytea_output
is set toescape
(the traditional PostgreSQL format).
Table 9-11. SQL Binary String Functions and Operators
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
| bytea | String concatenation | E'\\\\Post'::bytea || E'\\047gres\\000'::bytea | \\Post'gres\000 |
octet_length( | int | Number of bytes in binary string | octet_length(E'jo\\000se'::bytea) | 5 |
overlay( | bytea | Replace substring | overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) | T\\002\\003mas |
position( | int | Location of specified substring | position(E'\\000om'::bytea in E'Th\\000omas'::bytea) | 3 |
substring( | bytea | Extract substring | substring(E'Th\\000omas'::bytea from 2 for 3) | h\000o |
trim([both]
| 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-12. Some of them are used internally to implement the SQL-standard string functions listed in Table 9-11.
Table 9-12. Other Binary String Functions
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
btrim( | 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( | 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( | 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( | int | Extract bit from string | get_bit(E'Th\\000omas'::bytea, 45) | 1 |
get_byte( | int | Extract byte from string | get_byte(E'Th\\000omas'::bytea, 4) | 109 |
length( | int | Length of binary string | length(E'jo\\000se'::bytea) | 5 |
md5( | text | Calculates the MD5 hash of string , returning the result in hexadecimal | md5(E'Th\\000omas'::bytea) | 8ab2d3c9689aaf18 b4958c334c82d8b1 |
set_bit( | bytea | Set bit in string | set_bit(E'Th\\000omas'::bytea, 45, 0) | Th\000omAs |
set_byte( | 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 33.4.
Please login to continue.