This section describes functions and operators for examining
and manipulating values of type bytea.
SQL defines some string
functions with a special syntax where certain key words rather
than commas are used to separate the arguments. Details are in
Table
9-8. Some functions are also implemented using the regular
syntax for function invocation. (See Table
9-9.)
Table 9-8. SQL
Binary String Functions and Operators
| Function |
Return Type |
Description |
Example |
Result |
| string
|| string |
bytea |
String concatenation |
'\\\\Post'::bytea ||
'\\047gres\\000'::bytea |
\\Post'gres\000 |
octet_length(string) |
int |
Number of bytes in binary string |
octet_length(
'jo\\000se'::bytea) |
5 |
position(substring in string) |
int |
Location of specified substring |
position('\\000om'::bytea in
'Th\\000omas'::bytea) |
3 |
substring(string [from
int] [for int]) |
bytea |
Extract substring |
substring('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('\\000'::bytea from
'\\000Tom\\000'::bytea) |
Tom |
get_byte(string, offset) |
int |
Extract byte from string |
get_byte('Th\\000omas'::bytea,
4) |
109 |
set_byte(string, offset, newvalue) |
bytea |
Set byte in string |
set_byte('Th\\000omas'::bytea, 4,
64) |
Th\000o@as |
get_bit(string, offset) |
int |
Extract bit from string |
get_bit('Th\\000omas'::bytea,
45) |
1 |
set_bit(string, offset, newvalue) |
bytea |
Set bit in string |
set_bit('Th\\000omas'::bytea, 45,
0) |
Th\000omAs |
Additional binary string manipulation functions are available
and are listed in Table
9-9. Some of them are used internally to implement the
SQL-standard string functions
listed in Table
9-8.
Table 9-9. 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('\\000trim\\000'::bytea,
'\\000'::bytea) |
trim |
length(string) |
int |
Length of binary string |
length('jo\\000se'::bytea) |
5 |
md5(string) |
text |
Calculates the MD5 hash of string, returning the result in
hexadecimal |
md5('Th\\000omas'::bytea) |
8ab2d3c9689aaf18
b4958c334c82d8b1 |
decode(string text,
type text) |
bytea |
Decode binary string from string previously encoded with
encode. Parameter type is same
as in encode. |
decode('123\\000456',
'escape') |
123\000456 |
encode(string bytea,
type text) |
text |
Encode binary string to ASCII-only representation. Supported
types are: base64, hex, escape. |
encode('123\\000456'::bytea,
'escape') |
123\000456 |
