| PostgreSQL 8.2.6 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | Next |
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 | E'\\\\Post'::bytea || E'\047円gres\000円'::bytea | \\Post'gres000円 |
get_bit(string, offset) | int | Extract bit from string | get_bit(E'Th\000円omas'::bytea, 45) | 1 |
get_byte(string, offset) | int | Extract byte from string | get_byte(E'Th\000円omas'::bytea, 4) | 109 |
octet_length(string) | int | Number of bytes in binary string | octet_length( E'jo\000円se'::bytea) | 5 |
position(substring in string) | int | Location of specified substring | position(E'\000円om'::bytea in E'Th\000円omas'::bytea) | 3 |
set_bit(string,
offset, newvalue) | bytea | Set bit in string | set_bit(E'Th\000円omas'::bytea, 45, 0) | Th000円omAs |
set_byte(string,
offset, newvalue) | bytea | Set byte in string | set_byte(E'Th\000円omas'::bytea, 4, 64) | Th000円o@as |
substring(string [from int] [for int]) | bytea | Extract substring | substring(E'Th\000円omas'::bytea from 2 for 3) | h000円o |
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'\000円Tom\000円'::bytea) | Tom |
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(E'\000円trim\000円'::bytea, E'\000円'::bytea) | trim |
decode(string text,
type text)
| bytea | Decode binary string from string previously
encoded with encode. Parameter type is same as in encode.
| decode(E'123\000456円', 'escape') | 123000456円 |
encode(string bytea,
type text)
| text | Encode binary string to ASCII-only representation. Supported types are: base64, hex, escape. | encode(E'123\000456円'::bytea, 'escape') | 123000456円 |
length(string) | int | Length of binary string | length(E'jo\000円se'::bytea) | 5 |
md5(string) | text | Calculates the MD5 hash of string, returning the result in hexadecimal | md5(E'Th\000円omas'::bytea) | 8ab2d3c9689aaf18 b4958c334c82d8b1 |