Binary String Functions and Operators

This section describes functions and operators for examining and manipulating values of type bytea .

SQL defines some string functions that use key words, rather than commas, to separate arguments. Details are in Table 9-9 . PostgreSQL also provides versions of these functions that use the regular function invocation syntax (see Table 9-10 ).

Note: The sample results shown on this page assume that the server parameter bytea_output is set to escape (the traditional PostgreSQL format).

Table 9-9. SQL Binary String Functions and Operators

Function Return Type Description Example Result
string || string bytea String concatenation E'\\\\Post'::bytea || E'\\047gres\\000'::bytea \\Post'gres\000
octet_length( string ) int Number of bytes in binary string octet_length(E'jo\\000se'::bytea) 5
overlay( string placing string from int [ for int ]) bytea Replace substring overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) T\\002\\003mas
position( substring in string ) int Location of specified substring position(E'\\000om'::bytea in E'Th\\000omas'::bytea) 3
substring( string [ from int ] [ for int ]) bytea Extract substring substring(E'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(E'\\000'::bytea from E'\\000Tom\\000'::bytea) Tom

Additional binary string manipulation functions are available and are listed in Table 9-10 . Some of them are used internally to implement the SQL -standard string functions listed in Table 9-9 .

Table 9-10. 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(E'\\000trim\\000'::bytea, E'\\000'::bytea) trim
decode( string text , format text ) bytea Decode binary data from textual representation in string . Options for format are same as in encode . decode(E'123\\000456', 'escape') 123\000456
encode( data bytea , format text ) text Encode binary data into a textual representation. Supported formats are: base64 , hex , escape . escape converts zero bytes and high-bit-set bytes to octal sequences ( \ nnn ) and doubles backslashes. encode(E'123\\000456'::bytea, 'escape') 123\000456
get_bit( string , offset ) int Extract bit from string get_bit(E'Th\\000omas'::bytea, 45) 1
get_byte( string , offset ) int Extract byte from string get_byte(E'Th\\000omas'::bytea, 4) 109
length( string ) int Length of binary string length(E'jo\\000se'::bytea) 5
md5( string ) text Calculates the MD5 hash of string , returning the result in hexadecimal md5(E'Th\\000omas'::bytea) 8ab2d3c9689aaf18 b4958c334c82d8b1
set_bit( string , offset , newvalue ) bytea Set bit in string set_bit(E'Th\\000omas'::bytea, 45, 0) Th\000omAs
set_byte( string , offset , newvalue ) bytea Set byte in string set_byte(E'Th\\000omas'::bytea, 4, 64) Th\000o@as

get_byte and set_byte number the first byte of a binary string as byte 0. get_bit and set_bit number bits from the right within each byte; for example bit 0 is the least significant bit of the first byte, and bit 15 is the most significant bit of the second byte.

See also the aggregate function string_agg in Section 9.20 and the large object functions in Section 32.4 .