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