Skip to main content

SQL functions

This page lists the available Apache Druid ® SQL functions in alphabetical order. For more specific information, see the following pages:

info

Not all Druid SQL functions are available in Imply Polaris.

ABS​

ABS(<NUMERIC>)

Calculates the absolute value of a numeric expression.

ACOS​

ACOS(<NUMERIC>)

Calculates the arc cosine of a numeric expression.

ANY_VALUE​

ANY_VALUE(expr, [maxBytesPerValue, [aggregateMultipleValues]])

Returns any value of the specified expression.

APPROX_COUNT_DISTINCT​

APPROX_COUNT_DISTINCT(expr)

Counts distinct values of a regular column or a prebuilt sketch column.

APPROX_COUNT_DISTINCT_BUILTIN(expr)

Counts distinct values of a string, numeric, or hyperUnique column using Druid's built-in cardinality or hyperUnique aggregators.

APPROX_COUNT_DISTINCT_DS_HLL​

APPROX_COUNT_DISTINCT_DS_HLL(expr, [<NUMERIC>, <CHARACTER>])

Counts distinct values of an HLL sketch column or a regular column.

APPROX_COUNT_DISTINCT_DS_THETA​

APPROX_COUNT_DISTINCT_DS_THETA(expr, [<NUMERIC>])

Counts distinct values of a Theta sketch column or a regular column.

APPROX_QUANTILE​

APPROX_QUANTILE(expr, <NUMERIC>, [<NUMERIC>])

Deprecated in favor of APPROX_QUANTILE_DS.

APPROX_QUANTILE_DS​

APPROX_QUANTILE_DS(expr, <NUMERIC>, [<NUMERIC>])

Computes approximate quantiles on a Quantiles sketch column or a regular numeric column.

APPROX_QUANTILE_FIXED_BUCKETS​

APPROX_QUANTILE_FIXED_BUCKETS(expr, <NUMERIC>, <NUMERIC>, <NUMERIC>, <NUMERIC>, [<CHARACTER>])

Computes approximate quantiles on fixed buckets histogram column or a regular numeric column.

ARRAY[]​

ARRAY[expr1, expr2, ...]

Constructs a SQL ARRAY literal from the expression arguments. The arguments must be of the same type.

ARRAY_AGG​

ARRAY_AGG([DISTINCT] expr, [<NUMERIC>])

Returns an array of all values of the specified expression.

ARRAY_APPEND​

ARRAY_APPEND(arr1, expr)

Appends expr to arr, the resulting array type determined by the type of arr1.

ARRAY_CONCAT​

ARRAY_CONCAT(arr1, arr2)

Concatenates arr2 to arr1. The resulting array type is determined by the type of arr1.|

ARRAY_CONCAT_AGG​

ARRAY_CONCAT_AGG([DISTINCT] expr, [<NUMERIC>])

Concatenates array inputs into a single array.

ARRAY_CONTAINS​

ARRAY_CONTAINS(arr, expr)

If expr is a scalar type, returns true if arr contains expr. If expr is an array, returns 1 if arr contains all elements of expr. Otherwise returns false.

ARRAY_LENGTH​

ARRAY_LENGTH(arr)

Returns length of the array expression.

ARRAY_OFFSET​

ARRAY_OFFSET(arr, long)

Returns the array element at the 0-based index supplied, or null for an out of range index.

ARRAY_OFFSET_OF​

ARRAY_OFFSET_OF(arr, expr)

Returns the 0-based index of the first occurrence of expr in the array. If no matching elements exist in the array, returns null or -1 if druid.generic.useDefaultValueForNull=true (deprecated legacy mode).

ARRAY_ORDINAL​

ARRAY_ORDINAL(arr, long)

Returns the array element at the 1-based index supplied, or null for an out of range index.

ARRAY_ORDINAL_OF​

ARRAY_ORDINAL_OF(arr, expr)

Returns the 1-based index of the first occurrence of expr in the array. If no matching elements exist in the array, returns null or -1 if druid.generic.useDefaultValueForNull=true (deprecated legacy mode).

ARRAY_OVERLAP​

ARRAY_OVERLAP(arr1, arr2)

Returns true if arr1 and arr2 have any elements in common, else false.

SCALAR_IN_ARRAY​

SCALAR_IN_ARRAY(expr, arr)

Returns true if the scalar expr is present in arr. Otherwise, returns false if the scalar expr is non-null or UNKNOWN if the scalar expr is NULL.

Returns UNKNOWN if arr is NULL.

ARRAY_PREPEND​

ARRAY_PREPEND(expr, arr)

Prepends expr to arr at the beginning, the resulting array type determined by the type of arr.

ARRAY_SLICE​

ARRAY_SLICE(arr, start, end)

Returns the subarray of arr from the 0-based index start (inclusive) to end (exclusive). Returns null, if start is less than 0, greater than length of arr, or greater than end.

ARRAY_TO_MV​

ARRAY_TO_MV(arr)

Converts an ARRAY of any type into a multi-value string VARCHAR.

ARRAY_TO_STRING​

ARRAY_TO_STRING(arr, str)

Joins all elements of arr by the delimiter specified by str.

ASIN​

ASIN(<NUMERIC>)

Calculates the arc sine of a numeric expression.

ATAN​

ATAN(<NUMERIC>)

Calculates the arc tangent of a numeric expression.

ATAN2​

ATAN2(<NUMERIC>, <NUMERIC>)

Calculates the arc tangent of the two arguments.

AVG​

AVG(<NUMERIC>)

Calculates the average of a set of values.

BIT_AND​

BIT_AND(expr)

Performs a bitwise AND operation on all input values.

BIT_OR​

BIT_OR(expr)

Performs a bitwise OR operation on all input values.

BIT_XOR​

BIT_XOR(expr)

Performs a bitwise XOR operation on all input values.

BITWISE_AND​

BITWISE_AND(expr1, expr2)

Returns the bitwise AND between the two expressions, that is, expr1 & expr2.

BITWISE_COMPLEMENT​

BITWISE_COMPLEMENT(expr)

Returns the bitwise NOT for the expression, that is, ~expr.

BITWISE_CONVERT_DOUBLE_TO_LONG_BITS​

BITWISE_CONVERT_DOUBLE_TO_LONG_BITS(expr)

Converts the bits of an IEEE 754 floating-point double value to a long.

BITWISE_CONVERT_LONG_BITS_TO_DOUBLE​

BITWISE_CONVERT_LONG_BITS_TO_DOUBLE(expr)

Converts a long to the IEEE 754 floating-point double specified by the bits stored in the long.

BITWISE_OR​

BITWISE_OR(expr1, expr2)

Returns the bitwise OR between the two expressions, that is, expr1 | expr2.

BITWISE_SHIFT_LEFT​

BITWISE_SHIFT_LEFT(expr1, expr2)

Returns a bitwise left shift of expr1, that is, expr1 << expr2.

BITWISE_SHIFT_RIGHT​

BITWISE_SHIFT_RIGHT(expr1, expr2)

Returns a bitwise right shift of expr1, that is, expr1 >> expr2.

BITWISE_XOR​

BITWISE_XOR(expr1, expr2)

Returns the bitwise exclusive OR between the two expressions, that is, expr1 ^ expr2.

BLOOM_FILTER​

BLOOM_FILTER(expr, <NUMERIC>)

Computes a Bloom filter from values produced by the specified expression.

BLOOM_FILTER_TEST​

BLOOM_FILTER_TEST(expr, <STRING>)

Returns true if the expression is contained in a Base64-serialized Bloom filter.

BTRIM​

BTRIM(<CHARACTER>, [<CHARACTER>])

Trims characters from both the leading and trailing ends of an expression.

CASE​

CASE expr WHEN value1 THEN result1 \[ WHEN value2 THEN result2 ... \] \[ ELSE resultN \] END

Returns a result based on a given condition.

CAST​

CAST(value AS TYPE)

Converts a value into the specified data type.

CEIL (date and time)​

CEIL(<TIMESTAMP> TO <TIME_UNIT>)

Rounds up a timestamp by a given time unit.

CEIL (numeric)​

CEIL(<NUMERIC>)

Calculates the smallest integer value greater than or equal to the numeric expression.

CHAR_LENGTH​

CHAR_LENGTH(expr)

Alias for LENGTH.

CHARACTER_LENGTH​

CHARACTER_LENGTH(expr)

Alias for LENGTH.

COALESCE​

COALESCE(expr, expr, ...)

Returns the first non-null value.

CONCAT​

CONCAT(expr, expr...)

Concatenates a list of expressions.

CONTAINS_STRING​

CONTAINS_STRING(<CHARACTER>, <CHARACTER>)

Finds whether a string is in a given expression, case-sensitive.

COS​

COS(<NUMERIC>)

Calculates the trigonometric cosine of an angle expressed in radians.

COT​

COT(<NUMERIC>)

Calculates the trigonometric cotangent of an angle expressed in radians.

COUNT​

COUNT([DISTINCT] expr)

COUNT(*)

Counts the number of rows.

CUME_DIST​

CUME_DIST()

Returns the cumulative distribution of the current row within the window calculated as number of window rows at the same rank or higher than current row / total window rows. The return value ranges between 1/number of rows and 1.

CURRENT_DATE​

CURRENT_DATE

Returns the current date in the connection's time zone.

CURRENT_TIMESTAMP​

CURRENT_TIMESTAMP

Returns the current timestamp in the connection's time zone.

DATE_EXPAND​

Returns the current timestamp in the connection's time zone.

DATE_TRUNC​

DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)

Rounds down a timestamp by a given time unit.

DECODE_BASE64_COMPLEX​

DECODE_BASE64_COMPLEX(dataType, expr)

Decodes a Base64-encoded string into a complex data type, where dataType is the complex data type and expr is the Base64-encoded string to decode.

DECODE_BASE64_UTF8​

DECODE_BASE64_UTF8(expr)

Decodes a Base64-encoded string into a UTF-8 encoded string.

DEGREES​

DEGREES(<NUMERIC>)

Converts an angle from radians to degrees.

DENSE_RANK​

DENSE_RANK()

Returns the rank for a row within a window without gaps. For example, if two rows tie for a rank of 1, the subsequent row is ranked 2.

DIV​

DIV(x, y)

Returns the result of integer division of x by y.

info

The DIV function is not implemented in Druid versions 30.0.0 or earlier. Consider using SAFE_DIVIDE instead.

DS_CDF​

DS_CDF(expr, splitPoint0, splitPoint1, ...)

Returns a string representing an approximation to the Cumulative Distribution Function given the specified bin definition.

DS_GET_QUANTILE​

DS_GET_QUANTILE(expr, fraction)

Returns the quantile estimate corresponding to fraction from a quantiles sketch.

DS_GET_QUANTILES​

DS_GET_QUANTILES(expr, fraction0, fraction1, ...)

Returns a string representing an array of quantile estimates corresponding to a list of fractions from a quantiles sketch.

DS_HISTOGRAM​

DS_HISTOGRAM(expr, splitPoint0, splitPoint1, ...)

Returns a string representing an approximation to the histogram given the specified bin definition.

DS_HLL​

DS_HLL(expr, [lgK, tgtHllType])

Creates an HLL sketch on a column containing HLL sketches or a regular column.

DS_QUANTILE_SUMMARY​

DS_QUANTILE_SUMMARY(expr)

Returns a string summary of a quantiles sketch.

DS_QUANTILES_SKETCH​

DS_QUANTILES_SKETCH(expr, [k])

Creates a Quantiles sketch on a column containing Quantiles sketches or a regular column.

DS_RANK​

DS_RANK(expr, value)

Returns an approximate rank between 0 and 1 of a given value, in which the rank signifies the fraction of the distribution less than the given value.

DS_THETA​

DS_THETA(expr, [size])

Creates a Theta sketch on a column containing Theta sketches or a regular column.

DS_TUPLE_DOUBLES​

DS_TUPLE_DOUBLES(expr, [nominalEntries])

DS_TUPLE_DOUBLES(dimensionColumnExpr, metricColumnExpr, ..., [nominalEntries])

Creates a Tuple sketch which contains an array of double values as the Summary Object. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

DS_TUPLE_DOUBLES_INTERSECT​

DS_TUPLE_DOUBLES_INTERSECT(expr, ..., [nominalEntries])

Returns an intersection of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE​

DS_TUPLE_DOUBLES_METRICS_SUM_ESTIMATE(expr)

Computes approximate sums of the values contained within a Tuple sketch which contains an array of double values as the Summary Object.

DS_TUPLE_DOUBLES_NOT​

DS_TUPLE_DOUBLES_NOT(expr, ..., [nominalEntries])

Returns a set difference of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Object are preserved as is. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

DS_TUPLE_DOUBLES_UNION​

DS_TUPLE_DOUBLES_UNION(expr, ..., [nominalEntries])

Returns a union of Tuple sketches which each contain an array of double values as their Summary Objects. The values contained in the Summary Objects are summed when combined. If the last value of the array is a numeric literal, Druid assumes that the value is an override parameter for nominal entries.

EARLIEST​

EARLIEST(expr, [maxBytesPerValue])

Returns the value of a numeric or string expression corresponding to the earliest __time value.

EARLIEST_BY​

EARLIEST_BY(expr, timestampExpr, [maxBytesPerValue])

Returns the value of a numeric or string expression corresponding to the earliest time value from timestampExpr.

EXP​

EXP(<NUMERIC>)

Calculates e raised to the power of the numeric expression.

EXTRACT​

EXTRACT(<TIME_UNIT> FROM <TIMESTAMP>)

Extracts the value of some unit of the timestamp, optionally from a certain time zone, and returns the number.

FIRST_VALUE​

FIRST_VALUE(expr)

Returns the value evaluated for the expression for the first row within the window.

FLOOR (date and time)​

FLOOR(<TIMESTAMP> TO <TIME_UNIT>)

Rounds down a timestamp by a given time unit.

FLOOR (numeric)​

FLOOR(<NUMERIC>)

Calculates the largest integer value less than or equal to the numeric expression.

GREATEST​

GREATEST([expr1, ...])

Returns the maximum value from the provided arguments.

GROUPING​

GROUPING(expr, expr...)

Returns a number for each output row of a groupBy query, indicating whether the specified dimension is included for that row.

HLL_SKETCH_ESTIMATE​

HLL_SKETCH_ESTIMATE(expr, [round])

Returns the distinct count estimate from an HLL sketch.

HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS​

HLL_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, [numStdDev])

Returns the distinct count estimate and error bounds from an HLL sketch.

HLL_SKETCH_TO_STRING​

HLL_SKETCH_TO_STRING(expr)

Returns a human-readable string representation of an HLL sketch.

HLL_SKETCH_UNION​

HLL_SKETCH_UNION([lgK, tgtHllType], expr0, expr1, ...)

Returns a union of HLL sketches.

HUMAN_READABLE_BINARY_BYTE_FORMAT​

HUMAN_READABLE_BINARY_BYTE_FORMAT(value[, precision])

Converts an integer byte size into human-readable IEC format.

HUMAN_READABLE_DECIMAL_BYTE_FORMAT​

HUMAN_READABLE_DECIMAL_BYTE_FORMAT(value[, precision])

Converts a byte size into human-readable SI format.

HUMAN_READABLE_DECIMAL_FORMAT​

HUMAN_READABLE_DECIMAL_FORMAT(value[, precision])

Converts a byte size into human-readable SI format with single-character units.

ICONTAINS_STRING​

ICONTAINS_STRING(<expr>, str)

Finds whether a string is in a given expression, case-insensitive.

IPV4_MATCH​

IPV4_MATCH(address, subnet)

Returns true if the IPv4 address belongs to the subnet literal, else false.

IPV4_PARSE​

IPV4_PARSE(address)

Parses address into an IPv4 address stored as an integer.

IPV4_STRINGIFY​

IPV4_STRINGIFY(address)

Converts address into an IPv4 address in dot-decimal notation.

IPV6_MATCH​

IPV6_MATCH(address, subnet)

Returns true if the IPv6 address belongs to the subnet literal, else false.

JSON_KEYS​

JSON_KEYS(expr, path)

Returns an array of field names from expr at the specified path.

JSON_MERGE​

JSON_MERGE(expr1, expr2[, expr3 ...]) Merges two or more JSON STRING or COMPLEX<json> into one. Preserves the rightmost value when there are key overlaps. Returning always a COMPLEX<json> type.

JSON_OBJECT​

JSON_OBJECT(KEY expr1 VALUE expr2[, KEY expr3 VALUE expr4, ...])

Constructs a new COMPLEX<json> object. The KEY expressions must evaluate to string types. The VALUE expressions can be composed of any input type, including other COMPLEX<json> values. JSON_OBJECT can accept colon-separated key-value pairs. The following syntax is equivalent: JSON_OBJECT(expr1:expr2[, expr3:expr4, ...]).

JSON_PATHS​

JSON_PATHS(expr)

Returns an array of all paths which refer to literal values in expr in JSONPath format.

JSON_QUERY​

JSON_QUERY(expr, path)

Extracts a COMPLEX<json> value from expr, at the specified path.

JSON_QUERY_ARRAY​

JSON_QUERY_ARRAY(expr, path)

Extracts an ARRAY<COMPLEX<json>> value from expr at the specified path. If value is not an ARRAY, it gets translated into a single element ARRAY containing the value at path. The primary use of this function is to extract arrays of objects to use as inputs to other array functions.

JSON_VALUE​

JSON_VALUE(expr, path [RETURNING sqlType])

Extracts a literal value from expr at the specified path. If you specify RETURNING and an SQL type name (such as VARCHAR, BIGINT, DOUBLE, etc) the function plans the query using the suggested type. Otherwise, it attempts to infer the type based on the context. If it can't infer the type, it defaults to VARCHAR.

LAG​

LAG(expr[, offset])

If you do not supply an offset, returns the value evaluated at the row preceding the current row. Specify an offset number n to return the value evaluated at n rows preceding the current one.

LAST_VALUE​

LAST_VALUE(expr)

Returns the value evaluated for the expression for the last row within the window.

LATEST​

LATEST(expr, [maxBytesPerValue])

Returns the value of a numeric or string expression corresponding to the latest __time value.

LATEST_BY​

LATEST_BY(expr, timestampExpr, [maxBytesPerValue])

Returns the value of a numeric or string expression corresponding to the latest time value from timestampExpr.

LEAD​

LEAD(expr[, offset])

If you do not supply an offset, returns the value evaluated at the row following the current row. Specify an offset number n to return the value evaluated at n rows following the current one; if there is no such row, returns the given default value.

LEAST​

LEAST([expr1, ...])

Returns the minimum value from the provided arguments.

LEFT​

LEFT(expr, [length])

Returns the leftmost number of characters from an expression.

LENGTH​

LENGTH(expr)

Returns the length of the expression in UTF-16 encoding.

LN​

LN(expr)

Calculates the natural logarithm of the numeric expression.

LOG10​

LOG10(expr)

Calculates the base-10 of the numeric expression.

LOOKUP​

LOOKUP(<CHARACTER>, <CHARACTER>[, <CHARACTER>])

Looks up the expression in a registered query-time lookup table.

LOWER​

LOWER(expr)

Returns the expression in lowercase.

LPAD​

LPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])

Returns the leftmost number of characters from an expression, optionally padded with the given characters.

LTRIM​

LTRIM(<CHARACTER>, [<CHARACTER>])

Trims characters from the leading end of an expression.

MAX​

MAX(expr)

Returns the maximum value of a set of values.

MILLIS_TO_TIMESTAMP​

MILLIS_TO_TIMESTAMP(millis_expr)

Converts a number of milliseconds since epoch into a timestamp.

MIN​

MIN(expr)

Returns the minimum value of a set of values.

MOD​

MOD(x, y)

Calculates x modulo y, or the remainder of x divided by y.

MV_APPEND​

MV_APPEND(arr1, expr)

Adds the expression to the end of the array.

MV_CONCAT​

MV_CONCAT(arr1, arr2)

Concatenates two arrays.

MV_CONTAINS​

MV_CONTAINS(arr, expr)

Returns true if the expression is in the array, false otherwise.

MV_FILTER_NONE​

MV_FILTER_NONE(expr, arr)

Filters a multi-value expression to include no values contained in the array.

MV_FILTER_ONLY​

MV_FILTER_ONLY(expr, arr)

Filters a multi-value expression to include only values contained in the array.

MV_LENGTH​

MV_LENGTH(arr)

Returns the length of an array expression.

MV_OFFSET​

MV_OFFSET(arr, long)

Returns the array element at the given zero-based index.

MV_OFFSET_OF​

MV_OFFSET_OF(arr, expr)

Returns the zero-based index of the first occurrence of a given expression in the array.

MV_ORDINAL​

MV_ORDINAL(arr, long)

Returns the array element at the given one-based index.

MV_ORDINAL_OF​

MV_ORDINAL_OF(arr, expr)

Returns the one-based index of the first occurrence of a given expression.

MV_OVERLAP​

MV_OVERLAP(arr1, arr2)

Returns true if the two arrays have any elements in common, false otherwise.

MV_PREPEND​

MV_PREPEND(expr, arr)

Adds the expression to the beginning of the array.

MV_SLICE​

MV_SLICE(arr, start, end)

Returns a slice of the array from the zero-based start and end indexes.

MV_TO_ARRAY​

MV_TO_ARRAY(str)

Converts a multi-value string from a VARCHAR to a VARCHAR ARRAY.

MV_TO_STRING​

MV_TO_STRING(arr, str)

Joins all elements of the array together by the given delimiter.

NTILE​

NTILE(tiles)

Divides the rows within a window as evenly as possible into the number of tiles, also called buckets, and returns the value of the tile that the row falls into.

NULLIF​

NULLIF(value1, value2)

Returns NULL if two values are equal, else returns the first value.

NVL​

NVL(e1, e2)

Returns e2 if e1 is null, else returns e1.

PARSE_JSON​

PARSE_JSON(expr)

Parses expr into a COMPLEX<json> object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a VARCHAR or it is invalid JSON, this function will result in an error.

PARSE_LONG​

PARSE_LONG(<CHARACTER>, [<INTEGER>])

Converts a string into a BIGINT with the given base or into a DECIMAL data type if the base is not specified.

PERCENT_RANK​

PERCENT_RANK()

Returns the relative rank of the row calculated as a percentage according to the formula: RANK() OVER (window) / COUNT(1) OVER (window).

POSITION​

POSITION(<CHARACTER> IN <CHARACTER> [FROM <INTEGER>])

Returns the one-based index position of a substring within an expression, optionally starting from a given one-based index.

POWER​

POWER(expr, power)

Calculates a numerical expression raised to the specified power.

RADIANS​

RADIANS(expr)

Converts an angle from degrees to radians.

RANK​

RANK()

Returns the rank with gaps for a row within a window. For example, if two rows tie for rank 1, the next rank is 3.

REGEXP_EXTRACT​

REGEXP_EXTRACT(<CHARACTER>, <CHARACTER>, [<INTEGER>])

Applies a regular expression to the string expression and returns the _n_th match.

REGEXP_LIKE​

REGEXP_LIKE(<CHARACTER>, <CHARACTER>)

Returns true or false signifying whether the regular expression finds a match in the string expression.

REGEXP_REPLACE​

REGEXP_REPLACE(<CHARACTER>, <CHARACTER>, <CHARACTER>)

Replaces all occurrences of a regular expression in a string expression with a replacement string. The replacement string may refer to capture groups using $1, $2, etc.

REPEAT​

REPEAT(<CHARACTER>, [<INTEGER>])

Repeats the string expression an integer number of times.

REPLACE​

REPLACE(expr, pattern, replacement)

Replaces a pattern with another string in the given expression.

REVERSE​

REVERSE(expr)

Reverses the given expression.

RIGHT(expr, [length])

Returns the rightmost number of characters from an expression.

ROUND​

ROUND(expr[, digits])

Calculates the rounded value for a numerical expression.

ROW_NUMBER​

ROW_NUMBER()

Returns the number of the row within the window starting from 1.

RPAD​

RPAD(<CHARACTER>, <INTEGER>, [<CHARACTER>])

Returns the rightmost number of characters from an expression, optionally padded with the given characters.

RTRIM​

RTRIM(<CHARACTER>, [<CHARACTER>])

Trims characters from the trailing end of an expression.

SAFE_DIVIDE​

SAFE_DIVIDE(x, y)

Returns x divided by y, guarded on division by 0.

SIN​

SIN(expr)

Calculates the trigonometric sine of an angle expressed in radians.

SQRT​

SQRT(expr)

Calculates the square root of a numeric expression.

STDDEV​

STDDEV(expr)

Alias for STDDEV_SAMP.

STDDEV_POP​

STDDEV_POP(expr)

Calculates the population standard deviation of a set of values.

STDDEV_SAMP​

STDDEV_SAMP(expr)

Calculates the sample standard deviation of a set of values.

STRING_AGG​

STRING_AGG(expr, separator, [size])

Collects all values of an expression into a single string.

STRING_TO_ARRAY​

STRING_TO_ARRAY(str1, str2)

Splits str1 into an array on the delimiter specified by str2, which is a regular expression.

STRING_FORMAT​

STRING_FORMAT(pattern[, args...])

Returns a string formatted in accordance to Java's String.format method.

STRING_TO_MV​

STRING_TO_MV(str1, str2)

Splits str1 into an multi-value string on the delimiter specified by str2, which is a regular expression.

STRLEN​

STRLEN(expr)

Alias for LENGTH.

STRPOS​

STRPOS(<CHARACTER>, <CHARACTER>)

Returns the one-based index position of a substring within an expression.

SUBSTR​

SUBSTR(<CHARACTER>, <INTEGER>, [<INTEGER>])

Alias for SUBSTRING.

SUBSTRING​

SUBSTRING(<CHARACTER>, <INTEGER>, [<INTEGER>])

Returns a substring of the expression starting at a given one-based index.

SUM​

SUM(expr)

Calculates the sum of a set of values.

TAN​

TAN(expr)

Calculates the trigonometric tangent of an angle expressed in radians.

TDIGEST_GENERATE_SKETCH​

TDIGEST_GENERATE_SKETCH(expr, [compression])

Generates a T-digest sketch from values of the specified expression.

TDIGEST_QUANTILE​

TDIGEST_QUANTILE(expr, quantileFraction, [compression])

Returns the quantile for the specified fraction from a T-Digest sketch constructed from values of the expression.

TEXTCAT​

TEXTCAT(<CHARACTER>, <CHARACTER>)

Concatenates two string expressions.

THETA_SKETCH_ESTIMATE​

THETA_SKETCH_ESTIMATE(expr)

Returns the distinct count estimate from a Theta sketch.

THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS​

THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(expr, errorBoundsStdDev)

Returns the distinct count estimate and error bounds from a Theta sketch.

THETA_SKETCH_INTERSECT​

THETA_SKETCH_INTERSECT([size], expr0, expr1, ...)

Returns an intersection of Theta sketches.

THETA_SKETCH_NOT​

THETA_SKETCH_NOT([size], expr0, expr1, ...)

Returns a set difference of Theta sketches.

THETA_SKETCH_UNION​

THETA_SKETCH_UNION([size], expr0, expr1, ...)

Returns a union of Theta sketches.

TIME_CEIL​

TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])

Rounds up a timestamp by a given time period, optionally from some reference time or timezone.

TIME_EXTRACT​

TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])

Extracts the value of some unit of the timestamp and returns the number.

TIME_FLOOR​

TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])

Rounds down a timestamp by a given time period, optionally from some reference time or timezone.

TIME_FORMAT​

TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])

Formats a timestamp as a string.

TIME_IN_INTERVAL​

TIME_IN_INTERVAL(<TIMESTAMP>, <CHARACTER>)

Returns whether a timestamp is contained within a particular interval, formatted as a string.

TIME_PARSE​

TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])

Parses a string into a timestamp.

TIME_SHIFT​

TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])

Shifts a timestamp forwards or backwards by a given number of time units.

TIMESTAMP_TO_MILLIS​

TIMESTAMP_TO_MILLIS(<TIMESTAMP>)

Returns the number of milliseconds since epoch for the given timestamp.

TIMESTAMPADD​

TIMESTAMPADD(<unit>, <count>, <TIMESTAMP>)

Adds a certain amount of time to a given timestamp.

TIMESTAMPDIFF​

TIMESTAMPDIFF(<unit>, <TIMESTAMP>, <TIMESTAMP>)

Takes the difference between two timestamps, returning the results in the given units.

TO_JSON_STRING​

TO_JSON_STRING(expr)

Serializes expr into a JSON string.

TRIM​

TRIM([BOTH|LEADING|TRAILING] [<chars> FROM] expr)

Trims the leading or trailing characters of an expression.

TRUNC​

TRUNC(expr[, digits])

Alias for TRUNCATE.

TRUNCATE​

TRUNCATE(expr[, digits])

Truncates a numerical expression to a specific number of decimal digits.

TRY_PARSE_JSON​

TRY_PARSE_JSON(expr)

Parses expr into a COMPLEX<json> object. This operator deserializes JSON values when processing them, translating stringified JSON into a nested structure. If the input is not a VARCHAR or it is invalid JSON, this function will result in a NULL value.

UNNEST​

UNNEST(source_expression) as table_alias_name(column_alias_name)

Unnests a source expression that includes arrays into a target column with an aliased name.

UPPER​

UPPER(expr)

Returns the expression in uppercase.

VAR_POP​

VAR_POP(expr)

Calculates the population variance of a set of values.

VAR_SAMP​

VAR_SAMP(expr)

Calculates the sample variance of a set of values.

VARIANCE​

VARIANCE(expr)

Alias for VAR_SAMP.