This section describes functions and operators for examining and manipulating binary string values. Strings in this context mean 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
octet_length(string) integer Number of bytes in binary string octet_length('jo\000円se'::bytea) 5position(substring in string) integer Location of specified substring position('\000円om'::bytea in 'Th\000円omas'::bytea) 3substring(string [from integer] [for integer]) bytea Extract substring
substring('Th\000円omas'::bytea from 2 for 3) h000円otrim([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 '\000円Tom\000円'::bytea) Tomget_byte(string, offset) integer Extract byte from string.
get_byte('Th\000円omas'::bytea, 4) 109set_byte(string,
offset, newvalue) bytea Set byte in string.
set_byte('Th\000円omas'::bytea, 4, 64) Th000円o@asget_bit(string, offset) integer Extract bit from string.
get_bit('Th\000円omas'::bytea, 45) 1set_bit(string,
offset, newvalue) bytea Set bit in string.
set_bit('Th\000円omas'::bytea, 45, 0) Th000円omAsAdditional 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
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('\000円trim\000円'::bytea,'\000円'::bytea) trimlength(string) integer Length of binary string
length('jo\000円se'::bytea) 5encode(string bytea,
type text)
text Encode binary string to ASCII-only representation. Supported
types are: base64, hex, escape.
encode('123\000456円'::bytea, 'escape') 123000456円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') 123000456円