Binary functions and operators#
Binary operators#
The 
operator performs concatenation.
Binary functions#

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

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

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.

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.

reverse
(binary) → varbinary Returns
binary
with the bytes in reverse order.
Base64 encoding functions#
The Base64 functions implement the encoding specified in RFC 4648.

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

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

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

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

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

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

from_big_endian_32
(binary) → integer# Decodes the 32bit two’s complement bigendian
binary
. The input must be exactly 4 bytes.

to_big_endian_32
(integer) → varbinary# Encodes
integer
into a 32bit two’s complement bigendian format.

from_big_endian_64
(binary) → bigint# Decodes the 64bit two’s complement bigendian
binary
. The input must be exactly 8 bytes.

to_big_endian_64
(bigint) → varbinary# Encodes
bigint
into a 64bit two’s complement bigendian format.
Floatingpoint encoding functions#

from_ieee754_32
(binary) → real# Decodes the 32bit bigendian
binary
in IEEE 754 singleprecision floatingpoint format. The input must be exactly 4 bytes.

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

from_ieee754_64
(binary) → double# Decodes the 64bit bigendian
binary
in IEEE 754 doubleprecision floatingpoint format. The input must be exactly 8 bytes.

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

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
.

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
.

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

murmur3
(binary) → varbinary# Computes the 128bit MurmurHash3 hash of
binary
.SELECT murmur3(from_base64(‘aaaaaa’)); – ba 58 55 63 55 69 b4 2f 49 20 37 2c a0 e3 96 ef
HMAC functions#

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
.