Built-in functions
Applies to: Databricks SQL
Databricks Runtime
This article presents links to and descriptions of built-in operators and functions for strings and binary types, numeric scalars, aggregations, windows, arrays, maps, dates and timestamps, casting, CSV data, JSON data, XPath manipulation, and other miscellaneous functions.
Also see:
Operators and predicates
For information on how operators are parsed with respect to each other, see Operator precedence.
Operator |
Syntax |
Description |
---|---|---|
|
Returns the bitwise AND of |
|
|
Returns the logical AND of |
|
|
Returns |
|
|
Returns true if |
|
|
Returns the logical |
|
|
Tests whether |
|
|
Returns |
|
|
Returns value at |
|
|
Returns the bitwise exclusive |
|
|
Returns fields extracted from the |
|
|
Casts the value |
|
|
Returns the integral part of the division of |
|
|
Returns a |
|
|
Returns a |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns |
|
|
Returns |
|
|
Tests whether the arguments do (not) have different values where NULLs are considered as comparable values. |
|
|
Tests whether |
|
|
Returns |
|
|
Tests whether |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns the same result as the |
|
|
Returns |
|
|
Returns |
|
|
Returns |
|
|
Returns the subtraction of |
|
|
Returns the logical |
|
|
Returns the logical |
|
|
Returns the remainder after |
|
|
Returns the concatenation of |
|
|
Returns the bitwise |
|
|
Returns the sum of |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns true if |
|
|
Returns |
|
|
Returns the bitwise |
Operator precedence
Precedence |
Operator |
---|---|
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 |
|
9 |
|
10 |
|
11 |
|
12 |
|
String and binary functions
Function |
Description |
---|---|
Returns the concatenation of |
|
Decrypts a binary |
|
Encrypts a binary |
|
Returns the ASCII code point of the first character of |
|
Converts |
|
Returns the binary representation of |
|
Casts the value of |
|
Returns the bit length of string data or number of bits of binary data. |
|
Returns |
|
Returns the character at the supplied UTF-16 code point. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the position of the first occurrence of |
|
Returns the character at the supplied UTF-16 code point. |
|
Returns the concatenation of the arguments. |
|
Returns the concatenation strings separated by |
|
Returns |
|
Returns a cyclic redundancy check value of |
|
Translates binary |
|
Returns the binary representation of a string using the |
|
Returns |
|
Returns the position of a string within a comma-separated list of strings. |
|
Formats |
|
Formats |
|
Returns a formatted string from printf-style format strings. |
|
Converts |
|
Returns true if |
|
Returns |
|
Returns the (1-based) index of the first occurrence of |
|
Returns |
|
Returns the leftmost |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the character length of string data or number of bytes of binary data. |
|
Returns the Levenshtein distance between the strings |
|
Returns true if |
|
Returns the position of the first occurrence of |
|
Returns |
|
Returns |
|
Returns |
|
Returns a masked version of the input |
|
Returns an MD5 128-bit checksum of |
|
Returns the byte length of string data or number of bytes of binary data. |
|
Replaces |
|
Extracts a part from |
|
Returns the position of the first occurrence of |
|
Returns the position of the first occurrence of |
|
Returns a formatted string from printf-style format strings. |
|
Returns true if |
|
Returns true if |
|
Returns the number of times |
|
Extracts the first string in |
|
Extracts the all strings in |
|
Returns the position of the first substring in |
|
Replaces all substrings of |
|
Returns the first substring in |
|
Returns the string that repeats |
|
Replaces all occurrences of |
|
Returns a reversed string or an array with reverse order of elements. |
|
Returns the rightmost |
|
Returns true if |
|
Returns |
|
Returns |
|
Splits |
|
Returns a sha1 hash value as a hex string of |
|
Returns a sha1 hash value as a hex string of |
|
Returns a checksum of the SHA-2 family as a hex string of |
|
Returns the soundex code of the string. |
|
Returns a string consisting of |
|
Splits |
|
Splits |
|
Returns |
|
Casts the value |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns the substring of |
|
Returns |
|
Returns |
|
Returns an |
|
Trim characters from a string. |
|
Decrypts a binary |
|
Returns |
|
Returns |
|
Returns a decoded base64 string as binary. |
|
Converts hexadecimal |
|
Returns |
|
Translates a string back from application/x-www-form-urlencoded format. |
|
Translates a string into application/x-www-form-urlencoded format. |
Numeric scalar functions
Function |
Description |
---|---|
Returns the bitwise |
|
Returns |
|
Returns the bitwise |
|
Returns the negated value of |
|
Returns the subtraction of |
|
Returns the value of |
|
Returns the sum of |
|
Returns the remainder after |
|
Returns the bitwise exclusive |
|
Returns the bitwise AND of |
|
Returns |
|
Returns the absolute value of the numeric value in |
|
Returns the inverse cosine (arccosine) of |
|
Returns the inverse hyperbolic cosine of |
|
Returns the inverse sine (arcsine) of |
|
Returns the inverse hyperbolic sine of |
|
Returns the inverse tangent (arctangent) of |
|
Returns the angle in radians between the positive x-axis of a plane and the point specified by the coordinates ( |
|
Returns inverse hyperbolic tangent of |
|
Casts the value |
|
Returns the number of bits set in the argument. |
|
Returns the value of a bit in a binary representation of an integral numeric. |
|
Returns the value obtained by reversing the order of the bits in the argument. |
|
Returns the rounded |
|
Returns the cube root of |
|
Returns the smallest number not smaller than |
|
Returns the smallest number not smaller than |
|
Converts |
|
Converts the TIMESTAMP_NTZ |
|
Returns the cosine of |
|
Returns the hyperbolic cosine of |
|
Returns the cotangent of |
|
Returns the cosecant of |
|
Casts the value |
|
Converts radians to degrees. |
|
Returns the integral part of the division of |
|
Casts the value |
|
Returns the constant |
|
Returns |
|
Returns |
|
Returns the factorial of |
|
Casts the value |
|
Returns the largest number not smaller than |
|
Returns the value of a bit in a binary representation of an integral numeric. |
|
Returns |
|
Casts the value |
|
Returns |
|
Returns the natural logarithm (base |
|
Returns the logarithm of |
|
Returns |
|
Returns the logarithm of |
|
Returns the logarithm of |
|
Returns the remainder after |
|
Returns |
|
Returns the negated value of |
|
Returns pi. |
|
Returns the positive remainder after |
|
Returns the value of |
|
Raises |
|
Raises |
|
Converts |
|
Returns a random value between 0 and 1. |
|
Returns a random value from a standard normal distribution. |
|
Returns a random value between 0 and 1. |
|
Returns |
|
Returns the rounded |
|
Returns the secant of |
|
Returns the sine of |
|
Returns a bitwise left shifted by |
|
Returns a bitwise signed signed integral number right shifted by |
|
Returns a bitwise unsigned signed integral number right shifted by |
|
Returns -1.0, 0.0, or 1.0 as |
|
Returns -1.0, 0.0, or 1.0 as |
|
Returns the hyperbolic sine of |
|
Casts the value |
|
Returns the square root of |
|
Returns the tangent of |
|
Returns the hyperbolic tangent of |
|
Casts |
|
Returns |
|
Returns the sum of |
|
Returns |
|
Returns |
|
Returns the subtraction of |
|
Returns |
|
Returns the bucket number for a value in an equi-width histogram. |
Aggregate functions
Function |
Description |
---|---|
Returns true if at least one value of |
|
Returns any random value of |
|
Returns the estimated number of distinct values in |
|
Returns the approximate percentile of the |
|
Returns the top |
|
Returns an array consisting of all values in |
|
Returns the mean calculated from values of a group. |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns the bitwise |
|
Returns true if all values in |
|
Returns true if at least one value in |
|
Returns an array consisting of all values in |
|
Returns an array consisting of all unique values in |
|
Returns Pearson coefficient of correlation between a group of number pairs. |
|
Returns the total number of retrieved rows in a group, including rows containing null. |
|
Returns the number of rows in a group for which the supplied expressions are all non-null. |
|
Returns the number of true values for the group in |
|
Returns a count-min sketch of all values in the group in |
|
Returns the population covariance of number pairs in a group. |
|
Returns the sample covariance of number pairs in a group. |
|
Returns true if all values of |
|
Returns the first value of |
|
Returns the first value of |
|
Returns a HyperLogLog sketch used to approximate a distinct values count. |
|
Aggregates HyperLogLog sketches for a group of rows. |
|
Returns the kurtosis value calculated from values of a group. |
|
Returns the last value of |
|
Returns the last value of |
|
Returns the maximum value of |
|
Returns the value of an |
|
Returns the mean calculated from values of a group. |
|
Returns the median calculated from values of a group. |
|
Returns the minimum value of |
|
Returns the value of an |
|
Returns the most frequent, not |
|
Returns the exact percentile value of |
|
Returns the approximate percentile of the |
|
Returns the interpolated percentile of the |
|
Returns the discrete percentile of the |
|
Returns the mean of |
|
Returns the mean of |
|
Returns the number of non-null value pairs |
|
Returns the intercept of the uni-variate linear regression line in a group where |
|
Returns the coefficient of determination from values of a group where |
|
Returns the slope of the linear regression line of non-null value pairs |
|
Returns the sum of squares of the |
|
Returns the sum of products of |
|
Returns the sum of squares of the |
|
Returns the skewness value calculated from values of a group. |
|
Returns true if at least one value of |
|
Returns the sample standard deviation calculated from the values within the group. |
|
Returns the sample standard deviation calculated from the values within the group. |
|
Returns the population standard deviation calculated from values of a group. |
|
Returns the sample standard deviation calculated from values of a group. |
|
Returns the sum calculated from values of a group. |
|
Returns the mean calculated from values of a group, NULL if there is an overflow. |
|
Returns the sum calculated from values of a group, NULL if there is an overflow. |
|
Returns the population variance calculated from values of a group. |
|
Returns the sample variance calculated from values of a group. |
|
Returns the sample variance calculated from values of a group. |
Ranking window functions
Function |
Description |
---|---|
Returns the rank of a value compared to all values in the partition. |
|
Divides the rows for each window partition into n buckets ranging from 1 to at most |
|
Computes the percentage ranking of a value within the partition. |
|
Returns the rank of a value compared to all values in the partition. |
|
Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition. |
Analytic window functions
Function |
Description |
---|---|
Returns the position of a value relative to all values in the partition. |
|
Returns the value of |
|
Returns the value of |
|
Returns the value of |
Array functions
Function |
Description |
---|---|
Returns element at position |
|
Aggregates elements in an array using a custom aggregator. |
|
Returns an array with the elements in |
|
Returns |
|
Removes NULL values from |
|
Returns true if |
|
Removes duplicate values from |
|
Returns an array of the elements in |
|
Returns an expanded |
|
Returns an array of the elements in the intersection of |
|
Concatenates the elements of |
|
Returns the maximum value in |
|
Returns the minimum value in |
|
Returns the position of the first occurrence of |
|
Returns |
|
Removes all occurrences of |
|
Returns an array containing |
|
Returns the number of elements in |
|
Returns |
|
Returns an array of the elements in the union of |
|
Returns true if the intersection of |
|
Returns a merged array of structs in which the nth struct contains all Nth values of input arrays. |
|
Returns the size of |
|
Returns the concatenation of the arguments. |
|
Returns the element of an |
|
Returns true if |
|
Returns rows by un-nesting |
|
Returns rows by un-nesting |
|
Filters the array in |
|
Transforms an array of arrays into a single array. |
|
Tests whether |
|
Returns the element of an |
|
Explodes an array of structs into a table. |
|
Explodes an array of structs into a table with outer semantics. |
|
Returns rows by un-nesting the array with numbering of positions. |
|
Returns rows by un-nesting the array with numbering of positions using |
|
Aggregates elements in an array using a custom aggregator. |
|
Returns a reversed string or an array with reverse order of elements. |
|
Generates an array of elements from |
|
Returns a random permutation of the array in |
|
Returns the cardinality of |
|
Returns a subset of an array. |
|
Returns the array in |
|
Transforms elements in an array in |
|
Returns the element of an |
|
Merges the arrays in |
Map functions
Function |
Description |
---|---|
Returns value at |
|
Returns the size of |
|
Returns the value of |
|
Returns rows by un-nesting |
|
Returns rows by un-nesting |
|
Creates a map with the specified key-value pairs. |
|
Returns the union of all |
|
Returns |
|
Returns an unordered array of all entries in |
|
Filters entries in the map in |
|
Creates a map with a pair of the |
|
Creates a map created from the specified array of entries. |
|
Returns an unordered array containing the keys of |
|
Returns an unordered array containing the values of |
|
Merges |
|
Returns the cardinality of |
|
Returns a map after splitting |
|
Transforms keys in a map in |
|
Transforms values in a map in |
|
Returns the value of |
Date, timestamp, and interval functions
For information on date and timestamp formats, see Datetime patterns.
Function |
Description |
---|---|
Returns interval divided by |
|
Returns the negated value of |
|
Returns the subtraction of |
|
Returns the subtraction of |
|
Returns the value of |
|
Returns the sum of |
|
Returns |
|
Returns the absolute value of the interval value in |
|
Returns the date that is |
|
Returns the current date at the start of query evaluation. |
|
Returns the current date at the start of query evaluation. |
|
Returns the current timestamp at the start of query evaluation. |
|
Returns the current session local timezone. |
|
Casts the value |
|
Returns the date |
|
Converts a timestamp to a string in the format |
|
Creates a date from the number of days since |
|
Extracts a part of the date, timestamp, or interval. |
|
Returns the date |
|
Returns timestamp truncated to the unit specified in |
|
Returns the date |
|
Adds |
|
Returns the number of days from |
|
Returns the difference between two timestamps measured in |
|
Returns the day of month of the date or timestamp. |
|
Returns the day of month of the date or timestamp. |
|
Returns the day of week of the date or timestamp. |
|
Returns the day of year of the date or timestamp. |
|
Returns the integral part of the division of interval |
|
Returns |
|
Returns |
|
Returns a timestamp in |
|
Returns the hour component of a timestamp. |
|
Returns the last day of the month that the date belongs to. |
|
Creates a date from |
|
Creates an day-time interval from |
|
make_interval(years, months, weeks, days, hours, mins, secs) |
Deprecated: Creates an interval from |
Creates a timestamp from |
|
Creates a year-month interval from |
|
Returns the minute component of the timestamp in |
|
Returns the month component of the timestamp in |
|
Returns the number of months elapsed between dates or timestamps in |
|
Returns the first date which is later than |
|
Returns the current timestamp at the start of query evaluation. |
|
Returns the quarter of the year for |
|
Returns the second component of the timestamp in |
|
Creates a session-window over a timestamp expression. |
|
Returns -1.0, 0.0, or 1.0 as interval |
|
Returns -1.0, 0.0, or 1.0 as interval |
|
Casts |
|
Creates a timestamp |
|
Creates a timestamp |
|
Creates timestamp |
|
Adds |
|
Returns the difference between two timestamps measured in |
|
Returns |
|
Returns |
|
Returns the timestamp in |
|
Returns the timestamp in |
|
Returns a date with the a portion of the date truncated to the unit specified by the format model |
|
Returns the sum of |
|
Returns |
|
Returns |
|
Returns the subtraction of |
|
Returns |
|
Returns the number of days since |
|
Returns the number of microseconds since |
|
Returns the number of milliseconds since |
|
Returns the number of seconds since |
|
eturns the UNIX timestamp of current or specified time. |
|
Returns the day of the week of |
|
Returns the week of the year of |
|
Returns the year component of |
|
Creates a hopping based sliding-window over a timestamp expression. |
|
Returns the inclusive end time of a sliding-window produced by the window or session_window functions. |
H3 geospatial functions
For information about H3 geospatial functions, see H3 geospatial functions.
Cast functions and constructors
For information on casting between types, see cast function and try_cast function.
Function |
Description |
---|---|
Returns an array with the elements in |
|
Casts the value |
|
Casts the value of |
|
Casts |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Casts the value |
|
Creates a date from |
|
Creates an day-time interval from |
|
make_interval(years, months, weeks, days, hours, mins, secs) |
Creates an interval from |
Creates a timestamp from |
|
Creates a year-month interval from |
|
Creates a map with the specified key-value pairs. |
|
Creates a struct with the specified field names and values. |
|
Casts the value |
|
Casts the value |
|
Creates a STRUCT with the specified field values. |
|
Casts |
|
Casts |
|
Returns |
|
Returns |
|
Returns |
|
Returns |
|
Casts the value |
|
Returns |
CSV functions
Function |
Description |
---|---|
Returns a struct value with the |
|
Returns the schema of a CSV string in DDL format. |
|
Returns a CSV string with the specified struct value. |
JSON functions
Function |
Description |
---|---|
Returns fields extracted from the |
|
Returns a struct value with the |
|
Extracts a JSON object from |
|
Returns the number of elements in the outermost JSON array. |
|
Returns all the keys of the outermost JSON object as an array. |
|
Returns multiple JSON objects as a tuple. |
|
Returns the schema of a JSON string in DDL format. |
|
Returns a JSON string with the struct specified in |
XPath functions
Function |
Description |
---|---|
Returns values within the nodes of |
|
Returns |
|
Returns a DOUBLE value from an XML document. |
|
Returns a FLOAT value from an XML document. |
|
Returns a INTEGER value from an XML document. |
|
Returns a BIGINT value from an XML document. |
|
Returns a DOUBLE value from an XML document. |
|
Returns a SHORT value from an XML document. |
|
Returns the contents of the first XML node that matches the XPath expression. |
AI functions
Function |
Description |
---|---|
Returns text generated by a selected large language model (LLM) given the prompt. |
Miscellaneous functions
Function |
Description |
---|---|
Returns an error if |
|
Returns |
|
Returns |
|
Returns the file-level state of an autoloader |
|
Returns the first non-null argument. |
|
Creates a multi-dimensional cube using the specified expression columns. |
|
Returns the current catalog. |
|
Returns the current schema. |
|
Returns the current Unity Catalog Metastore id. |
|
Returns the current schema. |
|
Returns the current user. |
|
Returns the current version of Databricks. |
|
Returns the value matching the key. |
|
Returns the nth expression. |
|
Returns |
|
Returns the largest value of all arguments, skipping null values. |
|
Indicates whether a specified column in a |
|
Returns the level of grouping for a set of columns. |
|
Returns a hashed value of the arguments. |
|
Etimates number of distinct values collected in a HyperLogLog sketch. |
|
Combines two HyperLogLog sketches. |
|
Calls a method with reflection. |
|
Returns |
|
Returns |
|
Returns |
|
Returns the length in bytes of the block being read. |
|
Returns the start offset in bytes of the block being read. |
|
Returns the name of the file being read, or empty string if not available. |
|
Returns true if the current user is a member of group. |
|
Returns |
|
Returns |
|
Returns the smallest value of all arguments, skipping null values. |
|
Returns the keys which the user is authorized to see from Databricks secret service. |
|
Returns |
|
Returns monotonically increasing 64-bit integers. |
|
Returns |
|
Returns |
|
Returns |
|
Throws an exception with |
|
Returns a table of values within a specified range. |
|
Returns a table of values within a specified range. |
|
Calls a method with reflection. |
|
Extracts a secret value with the given |
|
Returns the current partition ID. |
|
Returns the set of SQL keywords in Databricks. |
|
Separates |
|
Returns a log of changes to a Delta Lake table with Change Data Feed enabled. |
|
Return a DDL-formatted type string for the data type of |
|
Returns the current user. |
|
Returns an universally unique identifier (UUID) string. |
|
Creates a hopping based sliding-window over a timestamp expression. |
|
Returns a 64-bit hashed value of the arguments. |
|
Returns the Apache Spark version. |