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 SQL-standard concatenation operator (||).
-
length(binary) → bigint Returns the length of
binaryin bytes.
-
lpad(binary, size, padbinary) → varbinary Left pads
binarytosizebytes withpadbinary. Ifsizeis less than the length ofbinary, the result is truncated tosizecharacters.sizemust not be negative andpadbinarymust be non-empty.
-
rpad(binary, size, padbinary) → varbinary Right pads
binarytosizebytes withpadbinary. Ifsizeis less than the length ofbinary, the result is truncated tosizecharacters.sizemust not be negative andpadbinarymust be non-empty.
-
substr(binary, start) → varbinary Returns the rest of
binaryfrom 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
binaryof lengthlengthfrom 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
binarywith 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
binaryinto a base64 string representation.
-
from_base64url(string) → varbinary# Decodes binary data from the base64 encoded
stringusing the URL safe alphabet.
-
to_base64url(binary) → varchar# Encodes
binaryinto 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
binaryinto a hex string representation.
Integer encoding functions#
-
from_big_endian_32(binary) → integer# Decodes the 32-bit two’s complement big-endian
binary. The input must be exactly 4 bytes.
-
to_big_endian_32(integer) → varbinary# Encodes
integerinto a 32-bit two’s complement big-endian format.
-
from_big_endian_64(binary) → bigint# Decodes the 64-bit two’s complement big-endian
binary. The input must be exactly 8 bytes.
-
to_big_endian_64(bigint) → varbinary# Encodes
bigintinto a 64-bit two’s complement big-endian format.
Floating-point encoding functions#
-
from_ieee754_32(binary) → real# Decodes the 32-bit big-endian
binaryin IEEE 754 single-precision floating-point format. The input must be exactly 4 bytes.
-
to_ieee754_32(real) → varbinary# Encodes
realinto a 32-bit big-endian binary according to IEEE 754 single-precision floating-point format.
-
from_ieee754_64(binary) → double# Decodes the 64-bit big-endian
binaryin IEEE 754 double-precision floating-point format. The input must be exactly 8 bytes.
-
to_ieee754_64(double) → varbinary# Encodes
doubleinto a 64-bit big-endian binary according to IEEE 754 double-precision floating-point format.
Hashing functions#
-
crc32(binary) → bigint# Computes the CRC-32 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 32-bit SpookyHashV2 hash of
binary.
-
spooky_hash_v2_64(binary) → varbinary# Computes the 64-bit SpookyHashV2 hash of
binary.
-
xxhash64(binary) → varbinary# Computes the xxHash64 hash of
binary.
-
murmur3(binary) → varbinary# Computes the 128-bit 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
binarywith the givenkey.
-
hmac_sha1(binary, key) → varbinary# Computes HMAC with SHA1 of
binarywith the givenkey.
-
hmac_sha256(binary, key) → varbinary# Computes HMAC with SHA256 of
binarywith the givenkey.
-
hmac_sha512(binary, key) → varbinary# Computes HMAC with SHA512 of
binarywith the givenkey.