Skip to main content
Functions for Base64 and hexadecimal encoding or decoding, computing hashes, and parsing strings into UUIDs.

​
md5

Computes the MD5 hash of a string. Syntax
Arguments
  • s — the string to hash.
Returned value
  • The raw 128-bit MD5 digest as a 16-byte binary value. Wrap it in hex() — or lower(hex(...)) to match PostgreSQL’s lowercase output — to render it as a hexadecimal string, as shown in the example.
Example
Hash a user's email

​
base64Encode

Encodes a string as Base64, following RFC 4648. Syntax
Arguments
  • plaintext — the string to encode.
Returned value
  • The Base64-encoded value as a string.
Example
Encode a string as Base64

​
base64Decode

Decodes a Base64-encoded string back to its original value, following RFC 4648. Raises an error if the input is not valid Base64. Syntax
Arguments
  • encoded — the Base64-encoded string to decode.
Returned value
  • The decoded value as a string.
Example
Decode a Base64 string

​
hex

Returns the hexadecimal representation of the argument, using uppercase letters A-F and no 0x prefix. For string inputs, every byte is encoded as two hexadecimal digits and leading zero bytes are preserved. For numeric inputs, the output starts at the most significant non-zero byte, so leading zero bytes are omitted. Syntax
Arguments
  • x — the value to encode. Accepts a string or a numeric value.
Returned value
  • The uppercase hexadecimal representation as a string.
Example
Hex-encode a string

​
unhex

Performs the inverse of hex: interprets each pair of hexadecimal digits as a byte and returns the decoded value. The input is case-insensitive. Syntax
Arguments
  • s — a string of hexadecimal digits.
Returned value
  • The decoded value as a string.
Example
Decode a hex string

​
to_hex

Returns the hexadecimal representation of an integer. The output is lowercase and leading zeros are stripped, matching PostgreSQL’s to_hex. Syntax
Arguments
  • n — the integer to convert.
Returned value
  • The lowercase hexadecimal representation as a string, without leading zeros.
Example
Convert an integer to hex

​
toUUID

Parses a string into a UUID value. Raises an error if the string is not a valid UUID. Syntax
Arguments
  • s — a string containing a UUID in the standard 8-4-4-4-12 hyphenated form.
Returned value
  • The parsed value as a UUID.
Example
Parse a string into a UUID

​
cityHash64

Computes a fast, non-cryptographic 64-bit CityHash of its arguments. Useful for bucketing or sampling, not for security. When multiple arguments are passed, their hashes are combined (ClickHouse only). Syntax
Arguments
  • arg1[, arg2, ...] — one or more values of any type to hash.
Returned value
  • The 64-bit hash as a UInt64.
Example
Bucket users into 10 groups by hash

​
touuidornull

Parses a string into a UUID, returning NULL instead of raising an error when the input is not a valid UUID (ClickHouse only). Syntax
Arguments
  • s — a string that may or may not contain a valid UUID.
Returned value
  • The parsed value as a UUID, or NULL if the input is not a valid UUID.
Example
Safely parse a possibly-invalid UUID

​
trybase64decode

Decodes a Base64-encoded string, returning an empty string instead of raising an error when the input is not valid Base64 (ClickHouse only). Syntax
Arguments
  • encoded — the string to decode.
Returned value
  • The decoded value as a string, or an empty string if the input is not valid Base64.
Example
Safely decode a possibly-invalid Base64 string