# 9.5. 位元字串函式及運算子

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

SQLdefines some string functions that use key words, rather than commas, to separate arguments. Details are in[Table 9.11](https://www.postgresql.org/docs/10/static/functions-binarystring.html#functions-binarystring-sql).PostgreSQLalso provides versions of these functions that use the regular function invocation syntax (see[Table 9.12](https://www.postgresql.org/docs/10/static/functions-binarystring.html#functions-binarystring-other)).

## Note

The sample results shown on this page assume that the server parameter[`bytea_output`](https://www.postgresql.org/docs/10/static/runtime-config-client.html#guc-bytea-output)is set to`escape`(the traditional PostgreSQL format).

**Table 9.11. SQLBinary 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 bytes appearing in`bytes`*\_from the start and end of*`string`\_ | `trim(E'\\000\\001'::bytea from E'\\000Tom\\001'::bytea)`                  | `Tom`                |                     |   |                          |                   |

Additional binary string manipulation functions are available and are listed in[Table 9.12](https://www.postgresql.org/docs/10/static/functions-binarystring.html#functions-binarystring-other). Some of them are used internally to implement theSQL-standard string functions listed in[Table 9.11](https://www.postgresql.org/docs/10/static/functions-binarystring.html#functions-binarystring-sql).

**Table 9.12. Other Binary String Functions**

| Function                               | Return Type | Description                                                                                                                                                                                             | Example                                                 | Result                              |
| -------------------------------------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------- | ----------------------------------- |
| `btrim(stringbytea`,`bytesbytea`)      | `bytea`     | Remove the longest string containing only bytes appearing in`bytes`*\_from the start and end of*`string`\_                                                                                              | `btrim(E'\\000trim\\001'::bytea, E'\\000\\001'::bytea)` | `trim`                              |
| `decode(stringtext`,`formattext`)      | `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(databytea`,`formattext`)       | `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](https://www.postgresql.org/docs/10/static/functions-aggregate.html)and the large object functions in[Section 34.4](https://www.postgresql.org/docs/10/static/lo-funcs.html).