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
4-8. Some functions are also implemented using the regular
syntax for function invocation. (See Table
4-9.)
Table 4-8. SQL
Binary String Functions and Operators
| Function |
Return Type |
Description |
Example |
Result |
string || string |
bytea |
string concatenation |
'\\\\Postgre'::bytea ||
'\\047SQL\\000'::bytea |
\\Postgre'SQL\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 |
Removes 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 below. Some of them are used internally to
implement the SQL-standard
string functions listed above.
Table 4-9. 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 |
Encodes 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 |
Decodes binary string from string previously encoded with
encode(). Parameter type is same as in encode(). |
decode('123\\000456',
'escape') |
123\000456 |
