| 
|
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 |
|
|
 |
|
|
