| PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 716 pages ISBN 0954612027 RRP £32.00 ($49.95) Sales of this book support the PostgreSQL project! Get a printed copy>>> |
7.5 Binary String Functions and Operators
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 7-8. Some functions are also implemented using the regular syntax for function invocation. (See Table 7-9.)
Table 7-8: SQL Binary String Functions and Operators
| Function | Return Type | Description
|
| bytea | String concatenation
e.g. E'\\\\Post'::bytea || E'\\047gres\\000'::bytea => \\Post'gres\000
|
get_bit(string, offset) | int | Extract bit from string
e.g. get_bit(E'Th\\000omas'::bytea, 45) => 1
|
get_byte(string, offset) | int | Extract byte from string
e.g. get_byte(E'Th\\000omas'::bytea, 4) => 109
|
| int | Number of bytes in binary string e.g. octet_length( E'jo\\000se'::bytea) => 5
|
| int | Location of specified substring e.g. position(E'\\000om'::bytea in E'Th\\000omas'::bytea) => 3
|
set_bit(string,
offset, newvalue) | bytea | Set bit in string
e.g. set_bit(E'Th\\000omas'::bytea, 45, 0) => Th\000omAs
|
set_byte(string,
offset, newvalue) | bytea | Set byte in string
e.g. set_byte(E'Th\\000omas'::bytea, 4, 64) => Th\000o@as
|
| bytea | Extract substring
e.g. substring(E'Th\\000omas'::bytea from 2 for 3) => h\000o
|
| bytea | Remove the longest string containing only the bytes in
bytes from the start
and end of stringe.g. trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) => Tom
|
Additional binary string manipulation functions are available and are listed in Table 7-9. Some of them are used internally to implement the SQL-standard string functions listed in Table 7-8.
Table 7-9: Other Binary String Functions
| Function | Return Type | Description
|
| bytea | Remove the longest string consisting only of bytes
in bytes from the start and end of
stringe.g. btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) => trim
|
| bytea | Decode binary string from string previously
encoded with encode. Parameter type is same as in encode.
e.g. decode(E'123\\000456', 'escape') => 123\000456
|
| text | Encode binary string to ASCII-only representation. Supported
types are: base64, hex, escape.
e.g. encode(E'123\\000456'::bytea, 'escape') => 123\000456
|
| int | Length of binary string
e.g. length(E'jo\\000se'::bytea) => 5
|
| text | Calculates the MD5 hash of string,
returning the result in hexadecimal
e.g. md5(E'Th\\000omas'::bytea) => 8ab2d3c9689aaf18 b4958c334c82d8b1
|
| ISBN 0954612027 | PostgreSQL Reference Manual - Volume 1 - SQL Language Reference | See the print edition |