Binary String Functions and Operators

PostgreSQL 7.3.4 Documentation
Prev | Chapter 6. Functions and Operators | Next

# 6.5. Binary String Functions and Operators

This section describes functions and operators for examining and manipulating binary string values. Strings in this context mean values of the 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 6-9. Some functions are also implemented using the regular syntax for function invocation. (See Table 6-10.)

Table 6-9. SQL Binary String Functions and Operators

Function | Return Type | Description | Example | Result
--- | --- | --- | --- | ---
string || string | `bytea` | String concatenation | '\\\\Post'::bytea || '\\047greSQL\\000'::bytea | \\Post'greSQL\000
`octet_length`(string) | `integer` | Number of bytes in binary string | octet_length('jo\\000se'::bytea) | 5
`position`(substring in string) | `integer` | Location of specified substring | position('\\000om'::bytea in 'Th\\000omas'::bytea) | 3
`substring`(string [from `integer`] [for `integer`]) | `bytea` | Extract substring | substring('Th\\000omas'::bytea from 2 for 3) | h\000o
`trim`([both] characters from string) | `bytea` | Remove the longest string containing only the characters from the beginning/end/both ends of the string | trim('\\000'::bytea from '\\000Tom\\000'::bytea) | Tom
`get_byte`(string, offset) | `integer` | 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) | `integer` | 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 6-10. Some of them are used internally to implement the SQL-standard string functions listed in Table 6-9.

Table 6-10. Other Binary String Functions

Function | Return Type | Description | Example | Result
--- | --- | --- | --- | ---
`btrim`(string `bytea` trim `bytea`) | `bytea` | Remove (trim) the longest string consisting only of characters in trim from the start and end of string. | btrim('\\000trim\\000'::bytea,'\\000'::bytea) | trim
`length`(string) | `integer` | Length of binary string | length('jo\\000se'::bytea) | 5
`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
`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

Prev | Home | Next
String Functions and Operators | Up | Pattern Matching