9.5. Binary String Functions and Operators

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	`E'\\\\Post'::bytea \|\| E'\\047gres\\000'::bytea`	`\\Post'gres\000`
`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`
`octet_length(string)`	`int`	Number of bytes in binary string	`octet_length( E'jo\\000se'::bytea)`	`5`
`position(substring in string)`	`int`	Location of specified substring	`position(E'\\000om'::bytea in E'Th\\000omas'::bytea)`	`3`
`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`
`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-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(E'\\000trim\\000'::bytea, E'\\000'::bytea)`	`trim`
`decode(string text, type text)`	`bytea`	Decode binary string from `string` previously encoded with `encode`. Parameter type is same as in `encode`.	`decode(E'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(E'123\\000456'::bytea, 'escape')`	`123\000456`
`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`

9.5. Binary String Functions and Operators

Ultimele discutii in forum

Ultimele articole

Membri recenti

Top membri

Newsletter

Parteneriat