> 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 dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
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=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.