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