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 - binaryto- sizebytes with- padbinary. If- sizeis less than the length of- binary, the result is truncated to- sizecharacters.- sizemust not be negative and- padbinarymust be non-empty.
- rpad(binary, size, padbinary) varbinary
- Right pads - binaryto- sizebytes with- padbinary. If- sizeis less than the length of- binary, the result is truncated to- sizecharacters.- sizemust not be negative and- padbinarymust be non-empty.
- substr(binary, start) varbinary
- Returns the rest of - binaryfrom the starting position- start, measured in bytes. Positions start with- 1. 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 length- lengthfrom the starting position- start, measured in bytes. Positions start with- 1. 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.
- from_base32(string) varbinary#
- Decodes binary data from the base32 encoded - string.
- to_base32(binary) varchar#
- Encodes - binaryinto a base32 string representation.
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, use- xxhash64(), 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 given- key.
- hmac_sha1(binary, key) varbinary#
- Computes HMAC with SHA1 of - binarywith the given- key.
- hmac_sha256(binary, key) varbinary#
- Computes HMAC with SHA256 of - binarywith the given- key.
- hmac_sha512(binary, key) varbinary#
- Computes HMAC with SHA512 of - binarywith the given- key.