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

Function	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)	Alias for to_base64.
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)	Alias for unbin.
from_hex(value)	Alias for unhex.
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)	Alias for hex.
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

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

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

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