:::info
Spice is built on Apache DataFusion and uses the PostgreSQL dialect, even when querying datasources with different SQL dialects.
:::
Scalar functions help transform, compute, and manipulate data at the row level. These functions are evaluated for each row in a query result and return a single value per invocation. Spice.ai supports a broad set of scalar functions, including math, string, conditional, date/time, array, struct, map, regular expression, and hashing functions. The function set closely follows the PostgreSQL dialect.
Math functions in Spice.ai SQL help perform numeric calculations, transformations, and analysis. These functions operate on numeric expressions, which can be constants, columns, or results of other functions and operators. The following math functions are supported:
absReturns the absolute value of a numeric expression. If the input is negative, the result is its positive equivalent; if the input is positive or zero, the result is unchanged.
acosReturns the arc cosine (inverse cosine) of a numeric expression. The input must be in the range [-1, 1]. The result is in radians.
acoshReturns the inverse hyperbolic cosine of a numeric expression. The input must be greater than or equal to 1.
asinReturns the arc sine (inverse sine) of a numeric expression. The input must be in the range [-1, 1]. The result is in radians.
asinhReturns the inverse hyperbolic sine of a numeric expression.
atanReturns the arc tangent (inverse tangent) of a numeric expression. The result is in radians.
atan2Returns the arc tangent of the quotient of its arguments, that is, atan(expression_y / expression_x). The result is in radians and takes into account the signs of both arguments to determine the correct quadrant.
atanhReturns the inverse hyperbolic tangent of a numeric expression. The input must be in the range (-1, 1).
cbrtReturns the cube root of a numeric expression.
ceilReturns the smallest integer greater than or equal to the input value.
cosReturns the cosine of a numeric expression, where the input is in radians.
coshReturns the hyperbolic cosine of a numeric expression.
cotReturns the cotangent of a numeric expression, where the input is in radians.
degreesConverts radians to degrees.
expReturns the value of e (Euler's number) raised to the power of the input value.
factorialReturns the factorial of a non-negative integer. For values less than 2, returns 1.
floorReturns the largest integer less than or equal to the input value.
gcdReturns the greatest common divisor of two integer expressions. If both inputs are zero, returns 0.
isnanReturns true if the input is NaN (not a number), otherwise returns false.
iszeroReturns true if the input is +0.0 or -0.0, otherwise returns false.
lcmReturns the least common multiple of two integer expressions. If either input is zero, returns 0.
lnReturns the natural logarithm (base e) of a numeric expression.
logReturns the logarithm of a numeric expression. If a base is provided, returns the logarithm to that base; otherwise, returns the base-10 logarithm.
log10Returns the base-10 logarithm of a numeric expression.
log2Returns the base-2 logarithm of a numeric expression.
nanvlReturns the first argument if it is not NaN; otherwise, returns the second argument.
piReturns an approximate value of π (pi).
pow and powerReturns the value of the first argument raised to the power of the second argument. pow is an alias for power.
radiansConverts degrees to radians.
randomReturns a random floating-point value in the range [0, 1). The random seed is unique for each row.
roundRounds a numeric expression to the nearest integer or to a specified number of decimal places.
signumReturns the sign of a numeric expression. Returns -1 for negative numbers, 1 for zero and positive numbers.
sinReturns the sine of a numeric expression, where the input is in radians.
sinhReturns the hyperbolic sine of a numeric expression.
sqrtReturns the square root of a numeric expression.
tanReturns the tangent of a numeric expression, where the input is in radians.
tanhReturns the hyperbolic tangent of a numeric expression.
truncTruncates a numeric expression to a whole number or to a specified number of decimal places. If decimal_places is positive, truncates digits to the right of the decimal point; if negative, truncates digits to the left.
Conditional functions help handle null values, select among alternatives, and compare multiple expressions. Functions such as coalesce, greatest, least, and nullif are supported. These are useful for data cleaning and conditional logic in queries.
String functions in Spice.ai SQL help manipulate, analyze, and transform text data. These functions operate on string expressions, which can be constants, columns, or results of other functions. The implementation closely follows the PostgreSQL dialect. The following string functions are supported:
asciiReturns the Unicode code point of the first character in a string. If the string is empty, returns 0.
Related function: chr
bit_lengthReturns the number of bits in the string. Each character is counted according to its byte representation (8 bits per byte).
Related functions: length, octet_length
btrimRemoves the longest string containing only characters in trim_str from the start and end of str. If trim_str is omitted, whitespace is removed.
Alternative syntax: trim(BOTH trim_str FROM str) or trim(trim_str FROM str)
Aliases: trim
Related functions: ltrim, rtrim
char_lengthAlias of character_length.
character_lengthReturns the number of characters in a string, not bytes. Handles Unicode correctly.
Aliases: length, char_length
Related functions: bit_length, octet_length
chrReturns the character with the specified Unicode code point.
Related function: ascii
concatConcatenates two or more strings into a single string.
Related function: concat_ws
concat_wsConcatenates strings using a separator between each value.
Related function: concat
containsReturns true if search_str is found within str. The search is case-sensitive.
ends_withReturns true if str ends with the substring substr.
find_in_setReturns the position (1-based) of str in the comma-separated list strlist. Returns 0 if not found.
initcapCapitalizes the first character of each word in the string. Words are delimited by non-alphanumeric characters.
Related functions: lower, upper
instrAlias of strpos.
leftReturns the first n characters from the left side of the string.
Related function: right
lengthAlias of character_length.
levenshteinReturns the Levenshtein distance between two strings.
lowerConverts all characters in the string to lower case.
Related functions: initcap, upper
lpadPads the left side of the string with another string until the result reaches the specified length. If the padding string is omitted, a space is used.
Related function: rpad
ltrimRemoves the longest string containing only characters in trim_str from the start of str. If trim_str is omitted, whitespace is removed.
Alternative syntax: trim(LEADING trim_str FROM str)
Related functions: btrim, rtrim
octet_lengthReturns the number of bytes in the string.
Related functions: bit_length, length
overlayReplaces a substring of str with substr, starting at position pos for count characters. If count is omitted, uses the length of substr.
positionAlias of strpos.
repeatReturns a string consisting of the input string repeated n times.
replaceReplaces all occurrences of substr in str with replacement.
reverseReturns the string with the character order reversed.
rightReturns the last n characters from the right side of the string.
Related function: left
rpadPads the right side of the string with another string until the result reaches the specified length. If the padding string is omitted, a space is used.
Related function: lpad
rtrimRemoves the longest string containing only characters in trim_str from the end of str. If trim_str is omitted, whitespace is removed.
Alternative syntax: trim(TRAILING trim_str FROM str)
Related functions: btrim, ltrim
split_partSplits the string on the specified delimiter and returns the substring at the given position (1-based).
starts_withReturns true if str starts with the substring substr.
strposReturns the position (1-based) of the first occurrence of substr in str. Returns 0 if not found.
Alternative syntax: position(substr in origstr)
Aliases: instr, position
substrExtracts a substring from str, starting at start_pos for length characters. If length is omitted, returns the rest of the string.
Alternative syntax: substring(str from start_pos for length)
Aliases: substring
substr_indexReturns the substring from str before or after a specified number of occurrences of the delimiter delim. If count is positive, returns everything to the left of the final delimiter (counting from the left). If count is negative, returns everything to the right of the final delimiter (counting from the right).
Aliases: substring_index
substringAlias of substr.
substring_indexAlias of substr_index.
to_hexConverts an integer to its hexadecimal string representation.
translateReplaces each character in str that matches a character in chars with the corresponding character in translation. If translation is shorter than chars, extra characters are removed.
trimAlias of btrim.
upperConverts all characters in the string to upper case.
Related functions: initcap, lower
uuidReturns a UUID v4 string value that is unique per row.
Binary string functions help encode and decode binary data, such as base64 and hexadecimal conversions. These are useful for working with encoded data or binary blobs.
Regular expression functions help match, extract, and replace patterns in strings. Spice.ai uses a PCRE-like regular expression syntax. Functions such as regexp_like, regexp_match, and regexp_replace are available.
Time and date functions help extract, format, and manipulate temporal data. Functions include current_date, now, date_part, date_trunc, and various conversion functions. These are essential for time series analysis and working with timestamps.
Array functions in Spice.ai SQL help construct, transform, and query array data types. These functions operate on array expressions, which can be constants, columns, or results of other functions. The implementation closely follows the PostgreSQL dialect. The following array functions are supported:
array_any_valueReturns the first non-null element in the array. If all elements are null, returns null.
array_appendAppends an element to the end of an array and returns the new array.
array_catAlias of array_concat.
array_concatConcatenates two or more arrays into a single array.
array_containsReturns true if the array contains the specified element.
Note: For array-to-array containment operations, use the @> operator.
array_dimsReturns an array of the array's dimensions. For a 2D array, returns the number of rows and columns.
array_distanceReturns the Euclidean distance between two input arrays of equal length.
array_distinctReturns a new array with duplicate elements removed, preserving the order of first occurrence.
array_elementExtracts the element at the specified index from the array. Indexing is 1-based.
Struct functions help construct and access structured data types (Arrow structs). Functions such as struct, named_struct, and get_field are supported. These are useful for working with nested or composite data.
Map functions help construct and query key-value data structures. Functions include map, map_extract, map_keys, and map_values. These are useful for semi-structured or JSON-like data.
Hashing functions help compute cryptographic hashes and checksums, such as md5, sha256, and digest. These are useful for data integrity, fingerprinting, and security applications.
Union functions help work with union (variant) data types, such as extracting the value or tag from a union. Functions include union_extract and union_tag.
Additional scalar functions include type casting, type inspection, and version reporting. Functions such as arrow_cast, arrow_typeof, and version are available.
Spice.ai aims for compatibility with PostgreSQL, but some functions or behaviors may differ depending on the underlying engine version.