Blob Functions

Search Shortcut cmd + k | ctrl + k

Documentation / SQL / Functions

Blob Functions

This section describes functions and operators for examining and manipulating BLOB values.

Name	Description
`arg1 \|\| arg2`	Concatenates two strings, lists, or blobs. Any `NULL` input results in `NULL`. See also `concat(arg1, arg2, ...)` and `list_concat(list1, list2)`.
`base64(blob)`	Converts a `blob` to a base64 encoded string.
`concat(value, ...)`	Concatenates multiple strings, lists, or blobs. `NULL` inputs are skipped. See also operator `\|\|`.
`decode(blob)`	Converts `blob` to `VARCHAR`. Fails if `blob` is not valid UTF-8.
`encode(string)`	Converts the `string` to `BLOB`. Converts UTF-8 characters into literal encoding.
`from_base64(string)`	Converts a base64 encoded `string` to a character string (`BLOB`).
`from_binary(value)`	Converts a `value` from binary representation to a blob.
`from_hex(value)`	Converts a `value` from hexadecimal representation to a blob.
`hex(blob)`	Converts `blob` to `VARCHAR` using hexadecimal encoding.
`md5(blob)`	Returns the MD5 hash of the `blob` as a `VARCHAR`.
`md5_number(blob)`	Returns the MD5 hash of the `blob` as a `HUGEINT`.
`octet_length(blob)`	Number of bytes in `blob`.
`read_blob(source)`	Returns the content from `source` (a filename, a list of filenames, or a glob pattern) as a `BLOB`. See the `read_blob` guide for more details.
`repeat(blob, count)`	Repeats the `blob` `count` number of times.
`sha1(blob)`	Returns a `VARCHAR` with the SHA-1 hash of the `blob`.
`sha256(blob)`	Returns a `VARCHAR` with the SHA-256 hash of the `blob`.
`to_base64(blob)`	Converts a `blob` to a base64 encoded string.
`to_hex(blob)`	Converts `blob` to `VARCHAR` using hexadecimal encoding.
`unbin(value)`	Converts a `value` from binary representation to a blob.
`unhex(value)`	Converts a `value` from hexadecimal representation to a blob.

`arg1 || arg2`

Description	Concatenates two strings, lists, or blobs. Any `NULL` input results in `NULL`. See also `concat(arg1, arg2, ...)` and `list_concat(list1, list2)`.
Example 1	`'Duck' \|\| 'DB'`
Result	`DuckDB`
Example 2	`[1, 2, 3] \|\| [4, 5, 6]`
Result	`[1, 2, 3, 4, 5, 6]`
Example 3	`'\xAA'::BLOB \|\| '\xBB'::BLOB`
Result	`\xAA\xBB`

`base64(blob)`

Description	Converts a `blob` to a base64 encoded string.
Example	`base64('A'::BLOB)`
Result	`QQ==`
Alias	`to_base64`

`concat(value, ...)`

Description	Concatenates multiple strings, lists, or blobs. `NULL` inputs are skipped. See also operator `\|\|`.
Example	`concat('Hello', ' ', 'World')`
Result	`Hello World`

`decode(blob)`

Description	Converts `blob` to `VARCHAR`. Fails if `blob` is not valid UTF-8.
Example	`decode('\xC3\xBC'::BLOB)`
Result	`ü`

`encode(string)`

Description	Converts the `string` to `BLOB`. Converts UTF-8 characters into literal encoding.
Example	`encode('my_string_with_ü')`
Result	`my_string_with_\xC3\xBC`

`from_base64(string)`

Description	Converts a base64 encoded `string` to a character string (`BLOB`).
Example	`from_base64('QQ==')`
Result	`A`

`from_binary(value)`

Description	Converts a `value` from binary representation to a blob.
Example	`from_binary('0110')`
Result	`\x06`
Alias	`unbin`

`from_hex(value)`

Description	Converts a `value` from hexadecimal representation to a blob.
Example	`from_hex('2A')`
Result	`*`
Alias	`unhex`

`hex(blob)`

Description	Converts `blob` to `VARCHAR` using hexadecimal encoding.
Example	`hex('\xAA\xBB'::BLOB)`
Result	`AABB`
Alias	`to_hex`

`md5(blob)`

Description	Returns the MD5 hash of the `blob` as a `VARCHAR`.
Example	`md5('\xAA\xBB'::BLOB)`
Result	`58cea1f6b2b06520613e09af90dc1c47`

`md5_number(blob)`

Description	Returns the MD5 hash of the `blob` as a `HUGEINT`.
Example	`md5_number('\xAA\xBB'::BLOB)`
Result	`94525045605907259200829535064523132504`

`octet_length(blob)`

Description	Number of bytes in `blob`.
Example	`octet_length('\xAA\xBB'::BLOB)`
Result	`2`

`read_blob(source)`

Description	Returns the content from `source` (a filename, a list of filenames, or a glob pattern) as a `BLOB`. See the `read_blob` guide for more details.
Example	`read_blob('hello.bin')`
Result	`hello\x0A`

`repeat(blob, count)`

Description	Repeats the `blob` `count` number of times.
Example	`repeat('\xAA\xBB'::BLOB, 5)`
Result	`\xAA\xBB\xAA\xBB\xAA\xBB\xAA\xBB\xAA\xBB`

`sha1(blob)`

Description	Returns a `VARCHAR` with the SHA-1 hash of the `blob`.
Example	`sha1('\xAA\xBB'::BLOB)`
Result	`65b1e351a6cbfeb41c927222bc9ef53aad3396b0`

`sha256(blob)`

Description	Returns a `VARCHAR` with the SHA-256 hash of the `blob`.
Example	`sha256('\xAA\xBB'::BLOB)`
Result	`d798d1fac6bd4bb1c11f50312760351013379a0ab6f0a8c0af8a506b96b2525a`

`to_base64(blob)`

Description	Converts a `blob` to a base64 encoded string.
Example	`to_base64('A'::BLOB)`
Result	`QQ==`
Alias	`base64`

`to_hex(blob)`

Description	Converts `blob` to `VARCHAR` using hexadecimal encoding.
Example	`to_hex('\xAA\xBB'::BLOB)`
Result	`AABB`
Alias	`hex`

`unbin(value)`

Description	Converts a `value` from binary representation to a blob.
Example	`unbin('0110')`
Result	`\x06`
Alias	`from_binary`

`unhex(value)`

Description	Converts a `value` from hexadecimal representation to a blob.
Example	`unhex('2A')`
Result	`*`
Alias	`from_hex`

About this page