Contents |
 |
|
|
|
 |
|
| |
This section describes functions and operators for examining and manipulating values of 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 9-8. Some functions are also implemented using the regular syntax for function invocation. (See Table 9-9.)
Table 9-8. SQL Binary String Functions and Operators
| Function |
Return Type |
Description |
Example |
Result |
|
string || string
|
bytea |
String concatenation
|
'\\\\Post'::bytea || '\\047gres\\000'::bytea
|
\\Post'gres\000
|
octet_length(string)
|
int
|
Number of bytes in binary string |
octet_length( 'jo\\000se'::bytea)
|
5
|
position(substring in string)
|
int
|
Location of specified substring |
position('\\000om'::bytea in 'Th\\000omas'::bytea)
|
3
|
substring(string [from int
] [for int
])
|
bytea
|
Extract substring
|
substring('Th\\000omas'::bytea from 2 for 3)
|
h\000o
|
trim([both] bytes from string) |
bytea
|
Remove the longest string containing only the bytes in bytes from the start and end of string |
trim('\\000'::bytea from '\\000Tom\\000'::bytea)
|
Tom
|
get_byte(string, offset) |
int
|
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) |
int
|
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 9-9. Some of them are used internally to implement the SQL-standard string functions listed in Table 9-8.
Table 9-9. Other Binary String Functions
| Function |
Return Type |
Description |
Example |
Result |
btrim(string bytea, bytes bytea)
|
bytea
|
Remove the longest string consisting only of bytes in bytes from the start and end of string |
btrim('\\000trim\\000'::bytea, '\\000'::bytea)
|
trim
|
length(string)
|
int
|
Length of binary string
|
length('jo\\000se'::bytea)
|
5
|
md5(string)
|
text
|
Calculates the MD5 hash of string, returning the result in hexadecimal |
md5('Th\\000omas'::bytea)
|
8ab2d3c9689aaf18 b4958c334c82d8b1
|
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
|
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
|
|
|
|
