10.11. Binary Functions and Operators#
Binary Operators#
The 
operator performs concatenation.
Binary Functions#

length
(binary) → bigint Returns the length of
binary
in bytes.

concat
(binary1, ..., binaryN) → varbinary Returns the concatenation of
binary1
,binary2
,...
,binaryN
. This function provides the same functionality as the SQLstandard concatenation operator (
).

substr
(binary, start) → varbinary Returns the rest of
binary
from the starting positionstart
, measured in bytes. Positions start with1
. A negative starting position is interpreted as being relative to the end of the string.

substr
(binary, start, length) → varbinary Returns a substring from
binary
of lengthlength
from the starting positionstart
, measured in bytes. Positions start with1
. A negative starting position is interpreted as being relative to the end of the string.

to_base64
(binary) → varchar# Encodes
binary
into a base64 string representation.

from_base64
(string) → varbinary# Decodes binary data from the base64 encoded
string
.

to_base64url
(binary) → varchar# Encodes
binary
into a base64 string representation using the URL safe alphabet.

from_base64url
(string) → varbinary# Decodes binary data from the base64 encoded
string
using the URL safe alphabet.

to_hex
(binary) → varchar# Encodes
binary
into a hex string representation.

from_hex
(string) → varbinary# Decodes binary data from the hex encoded
string
.

to_big_endian_64
(bigint) → varbinary# Encodes
bigint
in a 64bit 2’s complement big endian format.

from_big_endian_64
(binary) → bigint# Decodes
bigint
value from a 64bit 2’s complement big endianbinary
.

to_big_endian_32
(integer) → varbinary# Encodes
integer
in a 32bit 2’s complement big endian format.

from_big_endian_32
(binary) → integer# Decodes
integer
value from a 32bit 2’s complement big endianbinary
.

to_ieee754_32
(real) → varbinary# Encodes
real
in a 32bit bigendian binary according to IEEE 754 singleprecision floatingpoint format.

from_ieee754_32
(binary) → real# Decodes the 32bit bigendian
binary
in IEEE 754 singleprecision floatingpoint format.

to_ieee754_64
(double) → varbinary# Encodes
double
in a 64bit bigendian binary according to IEEE 754 doubleprecision floatingpoint format.

from_ieee754_64
(binary) → double# Decodes the 64bit bigendian
binary
in IEEE 754 doubleprecision floatingpoint format.

lpad
(binary, size, padbinary) → varbinary Left pads
binary
tosize
bytes withpadbinary
. Ifsize
is less than the length ofbinary
, the result is truncated tosize
characters.size
must not be negative andpadbinary
must be nonempty.

rpad
(binary, size, padbinary) → varbinary Right pads
binary
tosize
bytes withpadbinary
. Ifsize
is less than the length ofbinary
, the result is truncated tosize
characters.size
must not be negative andpadbinary
must be nonempty.

crc32
(binary) → bigint# Computes the CRC32 of
binary
. For general purpose hashing, usexxhash64()
, as it is much faster and produces a better quality hash.

md5
(binary) → varbinary# Computes the md5 hash of
binary
.

sha1
(binary) → varbinary# Computes the sha1 hash of
binary
.

sha256
(binary) → varbinary# Computes the sha256 hash of
binary
.

sha512
(binary) → varbinary# Computes the sha512 hash of
binary
.

xxhash64
(binary) → varbinary# Computes the xxhash64 hash of
binary
.

spooky_hash_v2_32
(binary) → varbinary# Computes the 32bit SpookyHashV2 hash of
binary
.

spooky_hash_v2_64
(binary) → varbinary# Computes the 64bit SpookyHashV2 hash of
binary
.

hmac_md5
(binary, key) → varbinary# Computes HMAC with md5 of
binary
with the givenkey
.

hmac_sha1
(binary, key) → varbinary# Computes HMAC with sha1 of
binary
with the givenkey
.

hmac_sha256
(binary, key) → varbinary# Computes HMAC with sha256 of
binary
with the givenkey
.

hmac_sha512
(binary, key) → varbinary# Computes HMAC with sha512 of
binary
with the givenkey
.