Binary, hash and UUID functions

Applies to VAST Cluster 5.5.

Bit strings, BLOB data and encodings, hashes, UUIDs and enums.

Each function has its own page: syntax, what it does, NULLs and edge cases, examples with their output, how it runs in VAST, how to make it faster, limitations and errors, and similar functions. Names that the VAST Query Engine doesn't have are in all functions A-Z.

Position of a value

Function	What it does
enum_code	Get the position of a value in its ENUM type, counting from 0.

Values of an ENUM type

Function	What it does
enum_first	Get the first value of an ENUM type.
enum_last	Get the last value of an ENUM type.
enum_range_boundary	List the values of an ENUM type from one value to another.
enum_range	List every value of an ENUM type, in its declared order.

Binary text

Function	What it does
bin (also `to_binary`)	Write the bytes of a string, or an integer, as text of 0s and 1s; to_binary is the same function.
from_binary (also `unbin`)	Read a string of 0s and 1s into a BLOB, padded on the left to whole bytes; unbin is the same function.

Bit strings

Function	What it does
bit_count	Count the bits set to 1 in a BIT value or in an integer with bit_count().
bit_position	Find where one bit string first occurs in another with bit_position().
BIT values and casts	Build BIT values from strings, integers and other types with CAST, read them back as text, and compare them.
bitstring	Pad a bit string on the left with 0s to a fixed length with bitstring().
Bitwise operators on BIT values	Combine, invert and shift BIT values with &, \|, xor(), ~, << and >>.
get_bit	Read one bit of a BIT value by its index with get_bit().
set_bit	Return a copy of a BIT value with one bit set to 0 or 1 with set_bit().

Bits of numbers

Function	What it does
signbit	Test whether the sign bit of a floating-point number is set, which tells -0.0 from 0.0, with signbit().

Binary data and encodings

Function	What it does
BLOB values, comparisons and \|\|	Write BLOB constants, cast to and from BLOB, compare BLOB values byte by byte and join them with \|\|.
decode	Read the bytes of a BLOB as UTF-8 text and return a VARCHAR.
encode	Turn a VARCHAR into its UTF-8 bytes, as a BLOB.
from_base64	Read Base64 text back into bytes, as a BLOB.
to_base64 (also `base64`)	Write the bytes of a BLOB as Base64 text; base64 is the same function.
from_hex (also `unhex`)	Turn hexadecimal text into a BLOB; unhex is the same function.
hex (also `to_hex`)	Write bytes, a string's UTF-8 bytes or an integer as upper-case hexadecimal text; to_hex is the same function.
octet_length	Count the bytes in a BLOB, or in the packed form of a BIT value.
repeat (BLOB)	Repeat the bytes of a BLOB a number of times.

Bitwise list aggregates

Function	What it does
list_bit_and	Combine the elements of an integer or BIT list with bitwise AND, one result per row.
list_bit_or	Combine the elements of an integer or BIT list with bitwise OR, one result per row.
list_bit_xor	Combine the elements of an integer or BIT list with bitwise XOR, one result per row.

Hashes

Function	What it does
md5_number_lower	Return the last 8 bytes of the MD5 digest of a string or BLOB as an unsigned 64-bit UBIGINT.
md5_number_upper	Return the first 8 bytes of the MD5 digest of a string or BLOB as an unsigned 64-bit UBIGINT.
md5_number	Compute the MD5 digest of a string or BLOB as a signed 128-bit HUGEINT.
md5	Compute the MD5 digest of a string or BLOB as 32 lower-case hex digits.
hash	Compute a 64-bit non-cryptographic hash of one or more values of any type, for bucketing and sampling.
sha1	Compute the SHA-1 digest of a VARCHAR or BLOB as 40 lowercase hex digits.
sha256	Compute the SHA-256 digest of a VARCHAR or BLOB as 64 lowercase hex digits.

UUIDs

Function	What it does
gen_random_uuid (also `uuid`)	Generate a random version 4 UUID per call and per row; uuid is the same function.
UUID values and casts	Write UUID constants, convert between UUID and text, and compare and sort UUID columns.