> For the complete documentation index, see [llms.txt](https://docs.postgresql.tw/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.postgresql.tw/11/the-sql-language/functions-and-operators/9.5.-wei-yuan-zi-chuan-han-shi-ji-yun-suan-zi.md).

# 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).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.postgresql.tw/11/the-sql-language/functions-and-operators/9.5.-wei-yuan-zi-chuan-han-shi-ji-yun-suan-zi.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.