6.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 theSQL-standard concatenation operator (||).

• substr(binary, start) → varbinary

• Returns the rest of binary from the starting position start,measured in bytes. Positions start with 1. A negative starting positionis interpreted as being relative to the end of the string.

• substr(binary, start, length) → varbinary

• Returns a substring from binary of length length from the startingposition start, measured in bytes. Positions start with 1. Anegative starting position is interpreted as being relative to the end ofthe string.

• tobase64(_binary) → varchar

• Encodes binary into a base64 string representation.

• frombase64(_string) → varbinary

• Decodes binary data from the base64 encoded string.

• tobase64url(_binary) → varchar

• Encodes binary into a base64 string representation using the URL safe alphabet.

• frombase64url(_string) → varbinary

• Decodes binary data from the base64 encoded string using the URL safe alphabet.

• tohex(_binary) → varchar

• Encodes binary into a hex string representation.

• fromhex(_string) → varbinary

• Decodes binary data from the hex encoded string.

• tobig_endian_64(_bigint) → varbinary

• Encodes bigint in a 64-bit 2’s complement big endian format.

• frombig_endian_64(_binary) → bigint

• Decodes bigint value from a 64-bit 2’s complement big endian binary.

• tobig_endian_32(_integer) → varbinary

• Encodes integer in a 32-bit 2’s complement big endian format.

• frombig_endian_32(_binary) → integer

• Decodes integer value from a 32-bit 2’s complement big endian binary.

• toieee754_32(_real) → varbinary

• Encodes real in a 32-bit big-endian binary according to IEEE 754 single-precision floating-point format.

• fromieee754_32(_binary) → real

• Decodes the 32-bit big-endian binary in IEEE 754 single-precision floating-point format.

• toieee754_64(_double) → varbinary

• Encodes double in a 64-bit big-endian binary according to IEEE 754 double-precision floating-point format.

• fromieee754_64(_binary) → double

• Decodes the 64-bit big-endian binary in IEEE 754 double-precision floating-point format.

• lpad(binary, size, padbinary) → varbinary

• Left pads binary to size bytes with padbinary.If size is less than the length of binary, the result istruncated to size characters. size must not be negativeand padbinary must be non-empty.

• rpad(binary, size, padbinary) → varbinary

• Right pads binary to size bytes with padbinary.If size is less than the length of binary, the result istruncated to size characters. size must not be negativeand padbinary must be non-empty.

• 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.

• xxhash64(binary) → varbinary

• Computes the xxhash64 hash of binary.

• spookyhash_v2_32(_binary) → varbinary

• Computes the 32-bit SpookyHashV2 hash of binary.

• spookyhash_v2_64(_binary) → varbinary

• Computes the 64-bit SpookyHashV2 hash of binary.

• hmacmd5(_binary, key) → varbinary

• Computes HMAC with md5 of binary with the given key.

• hmacsha1(_binary, key) → varbinary

• Computes HMAC with sha1 of binary with the given key.

• hmacsha256(_binary, key) → varbinary

• Computes HMAC with sha256 of binary with the given key.

• hmacsha512(_binary, key) → varbinary

• Computes HMAC with sha512 of binary with the given key.