This section describes functions and operators for examining and manipulating binary string values. Strings in this context include values of the type BYTEA.
SQL defines some string functions with a special syntax where certain keywords rather than commas are used to separate the arguments. Details are in Table 3-9. Some functions are also implemented using the regular syntax for function invocation. (See Table 3-10.)
Table 3-9. SQL Binary String Functions and Operators
| Function | Description | Example |
|---|---|---|
| string || string Returns bytea | string concatenation | '\\\\Postgre'::bytea || '\\047SQL\\000'::bytea Gives: \\Postgre'SQL\000 |
| octet_length(string) Returns integer | number of bytes in binary string | octet_length('jo\\000se'::bytea) Gives: 5 |
| position(substring in string) Returns integer | location of specified substring | position('\\000om'::bytea in 'Th\\000omas'::bytea) Gives: 3 |
| substring(string [from integer] [for integer]) Returns bytea | extract substring | substring('Th\\000omas'::bytea from 2 for 3) Gives: h\000o |
| trim ([both] characters from string) Returns bytea | Removes the longest string containing only the characters from the beginning, end, or both ends of the string. | trim('\\000'::bytea from '\\000Tom\\000'::bytea) Gives: Tom |
Additional binary string manipulation functions are available and are listed below. Some of them are used internally to implement the SQL-standard string functions listed above.
Table 3-10. Other Binary String Functions
| Function | Description | Example |
|---|---|---|
| btrim(string bytea, trim bytea) Returns 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) Gives: trim |
| length(string) Returns integer | length of binary string | length('jo\\000se'::bytea) Gives: 5 |
| encode(string bytea, type text) Returns text | Encodes binary string to ASCII-only representation. Supported types are: 'base64', 'hex', 'escape'. | encode('123\\000456'::bytea, 'escape') Gives: 123\000456 |
| decode(string text, type text) Returns bytea | Decodes binary string from string previously encoded with encode(). Parameter type is same as in encode(). | decode('123\\000456', 'escape') Gives: 123\000456 |